'                   DOCCO Check-Out Macros for Word
'                   (c) 2013, Our Community News, Inc., All Rights Reserved
'
'   These Visual Basic for Applications (VBA) macros are intended to help users who access shared documents in Dropbox avoid "conflicted copies."
'   A variant might be usable with other synchronization products such as Google Drive, Sugar Sync, or Box.com.
'   A document is checked-out to a person by recording their UserName in the document's "Manager" built-in property.
'   The "Manager" built-in property is set empty when the document is closed (i.e., checked-in).
'   Check-out and check-in only applies to documents stored in Dropbox.
'
'   System Requirements: Office 2010 on a PC or Office 2011 on a Mac
'       Might work on earlier versions of Office on PCs. Office 2008 for the Mac does not support VBA.
'
'   Install by using the VBA editor to copy the entire text into ThisDocument Excel Object in the documents you want to share.
'
' Versions:
'   1.0 January 20, 2013 (John Heiser)
'       Initial Release
'   2.0 February 2, 2013 (John Heiser)
'       Added bypass in the event the document was opened in protected mode (e.g., from an email attachment).
'       Added capture and checking of computer name in the "Company" built-in document property.
'       Added capture at check-out of date and time in the "Comments" built-in document property.
'       Added capture at check-in of check-out/check-in info in "Comments" with display with check-out question.
'

Private Function ComputerName() As String ' Return computer name
    If InStr(3, ActiveDocument.Path, ":") = 0 Then
        ComputerName = Environ$("computername") ' Running on Windows
    Else
        ComputerName = "Mac" ' Running on a Mac. For now, supply default name
    End If
End Function

Private Sub Document_Open() ' Document opened
   
    On Error GoTo ErrorHandler ' Bypass if document opened in protected mode (e.g., from an email attachment)
    
    If InStr(UCase(ActiveDocument.Path), "DROPBOX") > 0 Then ' Make sure the document is in Dropbox
   
        If ActiveDocument.BuiltInDocumentProperties("Manager") = "" Then ' If not checked-out
            
            If MsgBox("Do you want to check-out """ & ActiveDocument.Name & """?" & vbNewLine & vbNewLine & _
                ActiveDocument.BuiltInDocumentProperties("Comments"), vbYesNo + vbQuestion) = vbYes Then ' Ask about check-out
                
                ActiveDocument.BuiltInDocumentProperties("Manager") = Application.UserName ' Set Manager name to check it out
                ActiveDocument.BuiltInDocumentProperties("Comments") = "checked-out " & Now ' Save the date and time
                ActiveDocument.BuiltInDocumentProperties("Company") = ComputerName ' Save the computer name
                ActiveDocument.Saved = False ' Work around bug in Word - make sure document will be saved
                ActiveDocument.Save ' Save the file so it will be synched
                            
            Else
            
                MsgBox "When you are done viewing it, please close this document without saving changes.", vbInformation
            
            End If
        
        Else
            
            If ActiveDocument.BuiltInDocumentProperties("Manager") <> Application.UserName Or _
               ActiveDocument.BuiltInDocumentProperties("Company") <> ComputerName Then ' If checked-out to someone else or to this user on other computer
                
                MsgBox "This document is being edited by " & ActiveDocument.BuiltInDocumentProperties("Manager") & " (" & _
                    ActiveDocument.BuiltInDocumentProperties("Company") & ")." & vbNewLine & _
                    "It was " & ActiveDocument.BuiltInDocumentProperties("Comments") & "." & vbNewLine & _
                    "When you are done viewing it, please close this document without saving changes."
            
            Else
            
                MsgBox "This document is checked-out to you." & vbNewLine & _
                    "It was " & ActiveDocument.BuiltInDocumentProperties("Comments") & "." & vbNewLine & _
                    "To check it in, save changes when you close.", vbInformation
            
            End If
        
        End If
    
    End If
    
ErrorHandler:
    
End Sub

Private Sub Document_Close() ' Document being closed
    
    If InStr(UCase(ActiveDocument.Path), "DROPBOX") > 0 Then ' Make sure the document is in Dropbox
        
        If ActiveDocument.BuiltInDocumentProperties("Manager") = Application.UserName And _
           ActiveDocument.BuiltInDocumentProperties("Company") = ComputerName Then ' If checked-out to this user on this computer
                
            If ActiveDocument.Saved = False Then ' If there are changes to save
                
                If MsgBox("Do you want to save the changes you made to """ & ActiveDocument.Name & """?", vbYesNo + vbQuestion) = vbNo Then ' Want to discard the changes?
                
                    MsgBox "Changes will not be saved but this document will still be checked-out to you." & vbNewLine & _
                        "To check it in, please reopen it and save changes when you close.", vbInformation
                    ActiveDocument.Saved = True ' Make sure it isn't saved
                
                Else
                    
                    ActiveDocument.BuiltInDocumentProperties("Manager") = "" ' Check it in and save check-out/check-in info
                    ActiveDocument.BuiltInDocumentProperties("Comments") = "Last " & ActiveDocument.BuiltInDocumentProperties("Comments") & _
                        " by " & Application.UserName & " (" & ActiveDocument.BuiltInDocumentProperties("Company") & ")." & vbNewLine & _
                        "Checked-in " & Now & "."
                    ActiveDocument.BuiltInDocumentProperties("Company") = ""
                    ActiveDocument.Save ' Save the file so it will be synched
                
                End If
            
            Else ' No changes to save
            
                ActiveDocument.BuiltInDocumentProperties("Manager") = "" ' Check it in
                ActiveDocument.BuiltInDocumentProperties("Comments") = "Last " & ActiveDocument.BuiltInDocumentProperties("Comments") & _
                    " by " & Application.UserName & " (" & ActiveDocument.BuiltInDocumentProperties("Company") & ")." & vbNewLine & _
                    "Checked-in " & Now & "."
                ActiveDocument.BuiltInDocumentProperties("Company") = ""
                ActiveDocument.Saved = False ' Work around bug in Word - make sure document will be saved
                ActiveDocument.Save ' Save the file so it will be synched
            
            End If
                                                            
        Else
                
            If ActiveDocument.BuiltInDocumentProperties("Manager") = "" Then ' If not checked-out, give a generic warning
                
                MsgBox "Please close this document without saving changes.", vbExclamation
            
            Else ' Checked out to different person or on different computer. Give specific warning.
        
                MsgBox "This document is being edited by " & ActiveDocument.BuiltInDocumentProperties("Manager") & " (" & _
                    ActiveDocument.BuiltInDocumentProperties("Company") & ")." & vbNewLine & _
                    "It was " & ActiveDocument.BuiltInDocumentProperties("Comments") & "." & vbNewLine & _
                    "Please close this document without saving changes.", vbExclamation
            
            End If
            
        End If
    
    End If

End Sub