Generic Excel document

Question

I have Excel documents with macros with input and update formats for information, this document is used as a monthly database in which you can also run reports.

The problem is that when using a document, input and update parameters are sometimes simultaneously used, leading to loss of information. Both input and output are stored at the end of the macro to minimize losses, but I was wondering if there is any check at run time if the macro is being used by another user, and if so, the delay of the next macro is executed until while another user is finished?

Answer 1

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

• Create a temporary sheet and hide it
• When someone runs a macro, check to see if cell A1 is on this sheet or not.
• If it is empty, run the macro
• Before running the macro, write to this cell and after running the macro, clear the contents of another cell
• Your macro sandwich as below

the 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 After running the code, you cannot undo the changes because the code saves the file programmatically. The changes are permanent. In a file that is not shared, you can cancel by closing the file without saving and re-opening it.