Preventing activating another workbook while macro runs?

[dbrizor]

Is there a way to prevent a user from activating another excel workbook until a macro is finished running on the current one?

[Bernie Deitrick]

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:

Please Login or Register  to view this content.

This will write to cell A1 on a specific sheet in the currently active workbook:

Please Login or Register  to view this content.

This will write to cell A1 on a specific sheet in a specific workbook:

Please Login or Register  to view this content.

And you can easily convert your code to be specific by adding

Please Login or Register  to view this content.

And then any range or cell call, preceed with an S:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[dbrizor]

Great suggestion. But, would there be another simpler way besides changing all my code.

[Bernie Deitrick]

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.

[dbrizor]

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!

[Bernie Deitrick]

My point was that you cannot use the Workbook_Deactivate event because your macro is running.

[dbrizor]

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.

[Bernie Deitrick]

Every child object of the worksheet that can be specified must be, but that usually - for most macros - just means

Range(Range(.....),Range(....)) becomes S.Range(S.Range(.....),S.Range(....))

and Cells(.....) becomes S.Cells(.....)