VBA code to notify a user that an Excel document has already been checked out if they attempt to edit it

Question

I have a working, macro enabled Excel sheet on a SharePoint site for about 20 people to all use. I have it set so that it requires to be checked out in order to edit (So that only one person can edit at a time).

This has been working great, except some users of this file either ALWAYS forget to check the "check out and edit" option rather than "read only." So they will make changes in "Read Only" mode, and end up saving a copy of it on their own computers (because it wasn't checked out).

I was wondering if it were possible to make some sort of VBA event that checks to see if the document has been checked out of SharePoint before any changes are made, and if it hasn't, it shows a message box telling them so that they may close it out and re-open in "checked out" mode.

Answer 1

1 create a custom property in the workbook module of each spreadsheet

dim ischeckedout as boolean
Property Let Checkedout(b As Boolean)
     ischeckedout = b
End Property

2 edit your macro to set this property when the user checks out the spreadsheet

set wb = workbooks.open("myfile.xls")
wb.checkedout = true

3 back in the workbook.module of each spreadsheet create an event procedure that checks this property and responds appropriately

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not ischeckedout Then
    MsgBox "File Not Checked Out", vbOKOnly, "Close and Reopen"
End If
End Sub