Home  About  Advertise  Archive  Calendar  Contact  Help  Links  Maps  Subscribe  Topics  Updates
Our Community News - Home
CoMacs - Collaboration Macros


Any word     Help
All words
"..." for exact phrase
* and ? for wildcards
Category:
Results per page:
Sort by date

These Visual Basic for Applications (VBA) macros are intended to help users on PCs and Macs avoid conflicted copies when accessing shared files using Dropbox. Conflicted copies occur when Dropbox detects that a shared file has been simultaneously edited by more than one user.

These macros check-out a file to a user by recording information in the file's built-in properties and the file is then promptly saved and synchronized through Dropbox. The user's name is stored in the file's Manager built-in property. The name of the user's computer is stored in the Company field built-in property. Check-out/check-in dates and times are stored in the Comments field built-in property.

If another user attempts to open a file that has been checked-out to someone else or to that user on a different computer, the macros will display a message that the file is currently being edited and the name of the user and computer will be displayed.

When the first user completes editing the file and closes it saving changes (i.e., checks it in), the Manager and Company built-in properties are set empty and the file is promptly saved and synchronized through Dropbox thereby allowing someone else to check it out.

If a user checks-out a file and then closes it without saving changes, the changes are discarded but the file remains checked-out to that user. The user must reopen the file and close saving changes to check-in the file. Similarly, if a user checks-out a file and then uses File Save As to create a copy of the checked-out file, the original file will remain checked-out to that user until that user reopens the file and closes it saving changes.

The requirements for this to work are that the collaboration macros must be added to each shared file, all the users must enable macros and must be on-line whenever they are accessing shared files, and the Manager, Company, and Comments fields in the files' built-in properties must not be modified by users.

A detailed file listing in which the Comments column is included will show in that column which files are currently checked-out, when they were checked-out, and when the other files were last checked-out and who checked them out. This works properly for .XLSM and .DOCM files. File listings for .XLS and .DOC files show the date and time of latest check-out and who checked them out but often donít show any check-out information for files that are currently checked-out. Below is part of a Windows XP folder listing showing the Comments column. In this case, all the spreadsheets are checked-in except content1302.xlsm. Spreadsheets that don't contain the macros or that haven't yet been checked-out since the macros were installed have a blank Comments field.

System Requirements

Office 2010 on a PC or Office 2011 on a Mac. May work with earlier or later versions of Office on PCs. Office 2008 for the Mac does not support VBA, which is required to run the macros.

Remaining issues

  • Off-line use. If someone is not connected to the Internet, The macros will not be able to keep track of which files are currently checked-out and so a user may start editing a file only to discover later that someone else has edited the same file.
  • Near-Simultaneous Opens. In the event two users open the same file at just about the same time, both may be able to check-out the file. When a user checks-out a file, there is a delay of several seconds while Dropbox synchronizes the file across all the users that are on-line. During that time, another user may be able to check-out the same file. In that case, a conflicted copy of the file will be created by Dropbox within a few seconds and hopefully that will be noticed before much editing is done by either user.
  • Works only for Microsoft Excel spreadsheets and Microsoft Word documents. Files of type .XLSM or .DOCM are preferred as discussed above.
  • File Save As confuses the macros. If a user check-out a file and then use File Save As to save it under another name, the file under the new name will be checked-out to that user but the original file will not automatically be checked-in. To check-in the file, the user must reopen it and then close it saving changes. If someone has inadvertently left a file checked-out (e.g., and then gone on vacation), any user can force it to be checked-in by opening it and deleting the contents of the Manager field in the fileís built-in properties.
  • Mac computer name. When running on a Mac, this version of the macros always shows the computer name as "Mac."

The Macros

Disclaimer: This software is provided as-is with no explicit or implicit warranty of fitness for any purpose. Use it at your own risk.

The macros below are for shared Excel spreadsheets and Word documents. For full functionality, the files should be .XLSM or .DOCM although most features work with files of type .XLS or .DOC (see comments above about file listings).

Excel spreadsheets

XLSCO2.0.TXT - This text file contains the collaboration macros for Microsoft Excel spreadsheets.

Word documents

DOCCO2.0.TXT - This text file contains the collaboration macros for Microsoft Word documents. Note that to maintain Mac compatibility, these macros lack a BeforeSave event handler.

Installation

After downloading the files, install the macros by using the VBA Editor to copy the entire text into the ThisWorkbook Excel Object in the Excel spreadsheets you want to share and the ThisDocument Word Object in the Word documents you want to share. To remove the macros, delete them from the ThisWorkbook and ThisDocument objects in the files where you installed them.

Windows step-by-step spreadsheet macro installation

  1. Open the XLSCO2.0.TXT containing the spreadsheet macros and copy them to the clipboard
    The TXT file will typically open in Notepad.
  2. Use Excel to open the spreadsheet in which you want to install the macros
  3. Open the VBA Editor (VBE)
    The easiest way to do this is to type Alt-F11. If the Developer ribbon is displayed, you can also get to the VBE by clicking on the Visual Basic icon. If the Developer ribbon is not showing in Excel, you can enable the Developer ribbon by going to the File ribbon, selecting Options, selecting Customize Ribbon, and putting a check mark next to Developer.
  4. Open ThisWorkbook for the spreadsheet
    You may need to click on the "+" sign to the left of the VBAProject item for the spreadsheet, then click on the "+" sign to the left of the Microsoft Excel Objects folder, and finally double-click on ThisWorkbook. That will open a code-editing window to the right that has two dropdowns at the top showing "(General)" and "(Declarations)."
  5. Paste the macros into ThisWorkbook
    Click in the code window and paste in the macros.
  6. Save the spreadsheet
    You can type Ctrl-S or click on the File menu in the VBE and then select Save.
  7. Exit the Visual Basic Editor
    You can type Ctrl-Q, click on the X on the upper right corner of the VBE window, or click on the File menu in the VBE and then select Close and Return to Microsoft Excel.
  8. Close Excel
    If the message "Please close the spreadsheet without saving changes" is displayed, ignore it, and, if asked, save the changes.
  9. Test the spreadsheet
    Open the spreadsheet. If asked, click on Enable macros. A dialog box should be displayed that asks if you want to check-out the file.* Click Yes. If you have another computer with access to the same spreadsheet in Dropbox, use that computer to open the spreadsheet. You should get a message that the spreadsheet is being edited showing your log-in name and the name for your other computer. Make some insignificant change to the spreadsheet and close Excel. A dialog box should be displayed that asks if you want to save changes. Click Yes. Excel should exit and a folder listing that shows the Comments column (see above) should show your log-in name and computer name associated with the file. Open the file again, preferably on your second computer if you have one, reverse the insignificant change you made, and exit saving the file.

The sequence for Word documents and using Macintosh computers is very similar to the above.

* If the check-out dialog box is not displayed, it is likely your copy of Excel is set to disable macros without notification. On the Developer ribbon (see step 3 above if that Ribbon is not available), click on Macro Security. Select Disable all macros with notification or Enable all macros, the latter being less desirable because running macros from an unknown source can be a security risk. There is more information from Microsoft posted here.

Variants

It is likely that variants could be developed for Microsoft PowerPoint presentations and Microsoft Publisher files (versions 2003 and later). 

Alternate installation methods such as using templates, personal.xlsb, or creating add-ins may be more appropriate in some circumstances. The present method of copying and pasting the macros into the files to be shared has advantages in that most users don't have to be involved in installing any software, the effect of the macros is restricted to just those files in which they are installed, and updating the macros can be done by anyone with access to the Dropbox. On the other hand, creating an add-in offers the potential of checking-in files even in the case where the user elects to discard changes thereby reducing the burden on the user in those cases but it would require all users to install the add-in and subsequently install any updates to the add-in that are developed.

Variants of these macros might be usable with other synchronization products such as Google Drive, SugarSync, or Box.com.

Questions or Comments: johnheiser@ocn.me.

If this information was helpful to you, please consider making a donation.


Home  About  Advertise  Archive  Calendar  Contact  Donate  Help  Links  Maps  Subscribe  Topics  Updates
Contact us at (719) 488-3455, ads@ocn.me, editor@ocn.me, or P.O. Box 1742, Monument, Colorado 80132-1742.
This page was last modified on March 05, 2016. Home page: www.ocn.me
Copyright © 2001-2016 Our Community News, Inc. All Rights Reserved.