Determine if a workbook was opened by VBA code or by user

[KeepTrying]

Hi Folks,

It sounds simple but I couldn't find a proper way how to check if a workbook was opened by macro or by a user. To cut a long story short: I run a macro (it displays a userform) which opens several workbooks while running. How can I determine if a workbook is opened by a user during running? I want to prevent users to open a new/existing workbook while my macro is running.
Thanks in advance for your effort and time.

Kind Regards,

 

[JazzSP8]

I don't know if you can check for that as such.

However, one thing I do before running longer Macros is check to see if all the files I need are available. You can check to see if a Workbook is open in a few ways, here is one thread on it;

https://www.mrexcel.com/forum/excel-questions/10398-vba-check-if-workbook-open.html

You could have your macro check all Workbooks and then open them if they are free, locking it down for your Macro to use. If they aren't free just stop your Macro from going any further.

Alternatively, if you just want to retrieve information from the Workbooks that might already be in use, then you can have your Macro open them as Read Only and then it won't matter if someone else has the Workbook open already.

 

[KeepTrying]

Hi JazzSP8,

Thanks for your ideas. Necessary files are not fixed (e.g. use latest file in a folder). As mentioned "I want to prevent users to open a new/existing workbook while my macro is running." I have the simple way to open a new/existing workbook in a new instance (while macro is running) but that's just a workaround:

Set xlApp = New Excel.Application

 

[KeepTrying]

Bump
Anyone please? Maybe it's possible to determine it with some nice API code?

 

[Jaafar Tribak]

Bump
Anyone please? Maybe it's possible to determine it with some nice API code?

This got me thinking .

The only possible workaround that I can think of is to use a global hook namely the WH_CALLWNDPROC hook which the system calls before passing the message to the receiving window procedure.... Once this hook is set, one should, in theory, be able to trap the WM_CREATE message and if it is a workbook that is being created abort its creation.

This would be a convoluted way of doing things and the hook code would need to be ran from a dll or from a hidden second excel instance to prevent potential crashing.

Are the workbooks that the userform opens while it is running always the same and are they known before hand or not ? I am asking this to exclude those specific workbooks from the rest of the not-to-open workbooks.

 

[KeepTrying]

Dear Jaafar,
Thanks a lot for dropping by I wouldn't have thought this determination can be so complex.

Are the workbooks that the userform opens while it is running always the same and are they known before hand or not ?

Workbooks - which are opened by userform - are not always the same but if you can create a solution for fixed workbooks I'd be really grateful.

Thank you.

 

[mikerickson]

If you have the "prevent user from opening any workbook" code, I'd go with Jazz's notion.

At the start of your macro, open all the files you need. Set PreventOpening to True, run your macro, and then set PreventOpening to False. If you need to open a workbook in the middle of your macro, you could toggle PreventOpening as needed.

I know my explanation is cast as a non-existant property, but the equivalent can be done in any kind of environment. The key is that you (and your macro) control when you execute your "prevent the user from opening a workbook" code.

 

[KeepTrying]

Dear mikerickson,

Thank you so much for your advice. I'll consider your (and JazzSP8's) note as an alternative solution. I wrote earlier:

Necessary files are not fixed (e.g. use latest file in a folder).

But of course I can check those files in advance as well before I start to run my code.

 

[mikerickson]

I work in a Mac environment, where multiple instances of the Excel application is not possible.
This is what I was thinking.
in a class module

'  in class module for Class1

Public WithEvents Application As Application
Public BlockOpeningWorkbook As Boolean

Private Sub Application_NewWorkbook(ByVal Wb As Workbook)
    If BlockOpeningWorkbook Then
        Wb.Close savechanges:=False
        MsgBox "You cannot open or create a workbook at this time."
    End If
End Sub

Private Sub Application_WorkbookOpen(ByVal Wb As Workbook)
    Call Application_NewWorkbook(Wb)
End Sub

And these is a normal module. Call BlockingOn and BlockingOff as needed.

' in normal module

Public myApplication As Class1

Sub BlockingOn()
    If myApplication Is Nothing Then
        Set myApplication = New Class1
        Set myApplication.Application = ThisWorkbook.Application
    End If
    myApplication.BlockOpeningWorkbook = True
End Sub

Sub BlockingOff()
    If myApplication Is Nothing Then
        Set myApplication = New Class1
        Set myApplication.Application = ThisWorkbook.Application
    End If
    myApplication.BlockOpeningWorkbook = False
End Sub

The only issue might be that if blocking is on and the user tries to open a workbook that has macros, the Enable Macros? dialog appears.

 

[KeepTrying]

Hi mikerickson,

Thanks for your code it works like a treat but only if I do not run macro. If I run a macro and open another Excel file (e.g. from Windows Explorer) then new file opens in new instance without any problem (I use Office 365 so this is by default that opening a new file will run in new instance).
But your code is great, I use this Application Event as well form time to time.