Shared Excel Document

Question

I've got a Excel documents with macros with input and update forms for information, the document is used as a monthly database where you can run reports from as well.

The issue is that when using the document sometimes the input and update options are used at the same time causing information loss. Both the input and output save at the end of the macro to minimise the losses, but I was wondering if there is anyway of checking at runtime if there is a macro being use by another user and if so delay the next macro run until the other user is finished?

Answer 1

There is one way I can think of. Logically it should work. However I have not tested it.

1. Create a temp sheet and hide it
2. When anyone runs a macro, check if cell A1 of that sheet is empty or not
3. If it is empty then run the macro
4. Before running the macro, write to that cell and once the macro is run, clear the contents of the other cell
5. Sandwich your macro code as mentioned below

Code

Sub Sample()
    Dim ws As Worksheet

    ThisWorkbook.Save
    Doevents

    Set ws = ThisWorkbook.Sheets("HiddenSheetName")

    If Len(Trim(ws.Range("A1").Value)) = 0 Then
        ws.Range("A1").Value = "Macro Starts"
        ThisWorkbook.Save
        Doevents

        '
        '~~> Rest of your code goes here
        '

        ws.Range("A1").ClearContents
        ThisWorkbook.Save
        Doevents
    Else
        MsgBox "Please try after some time. There is a macro running... Blah Blah"
    End If
End Sub

CAUTION: Once the code runs, you cannot undo the changes since the code save the file programatically. The changes are permanent. In a file which is not shared, you can undo by closing the file without saving and re-opening it.