Is there a way to prevent a user from activating another excel workbook until a macro is finished running on the current one?
Is there a way to prevent a user from activating another excel workbook until a macro is finished running on the current one?
The best way to write your code is to make sure you fully specify the object you are working with.
This will write to cell A1 on the currently active sheet, no matter what that is:
This will write to cell A1 on a specific sheet in the currently active workbook:Range("A1").Value = 3
This will write to cell A1 on a specific sheet in a specific workbook:Worksheets("Sheet1").Range("A1").Value = 3
And you can easily convert your code to be specific by addingWorkbooks("File name.xlsm").Worksheets("Sheet1").Range("A1").Value = 3
And then any range or cell call, preceed with an S:Dim W As Workbook Dim S As Worksheet Set W = Workbooks("File name.xlsm") Set S = W.Worksheets("Sheet1")
orS.Range("A1").Value = 3
With S For Each r In .Range(.Range("A1"), .Cells(.Rows.Count,"A").End(xlup)) .... Next r End With
Bernie Deitrick
Excel MVP 2000-2010
Great suggestion. But, would there be another simpler way besides changing all my code.
Not really, unless you close all other visible workbooks with code first. Any solutions that could use macros or events are prevented from working because the macro is running, and only one macro can run at a time. Otherwise, just train your users to not do what you don't want them to do.
What objects would require specifying when Workbook_Deactivate() occurs to avoid errors? Is there a list somewhere with this information that you know of? Thanks in advance for the help too!
My point was that you cannot use the Workbook_Deactivate event because your macro is running.
Right. I meant, when using this:
Dim W As Workbook
Dim S As Worksheet
Set W = Workbooks("File name.xlsm")
Set S = W.Worksheets("Sheet1")
S.Range("A1").Value = 3
What objects would I need to identify specifically? Just Ranged areas? I only want to specify what I need to in my code to save time.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks