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,

 

[Jaafar Tribak]

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


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.

Hi KeepTrying,

I took a look at the WH_CALLWNDPROC hook approach , unfortunately it only works with Top-level windows ... Workbook windows are child windows so it won't work to trap the opening of workbooks.

I am assuming that the userform that is running is Modeless - Can you confirm that ?

 

[KeepTrying]

Hi Jaafar,

I am assuming that the userform that is running is Modeless - Can you confirm that ?

Yes, I confirm that.

Thank you.

 

[Jaafar Tribak]

Hi Jaafar,


Yes, I confirm that.

Thank you.

Following on mikerickson's steps, here is an alternative that should also prevent the user from opening files in other excel instances while the userform is running.

Code in the UserForm module :

Option Explicit

Private WithEvents app As Application
Private bIgnoreRemoteRequests As Boolean


Private Sub UserForm_Initialize()
    bIgnoreRemoteRequests = Application.IgnoreRemoteRequests
    Application.IgnoreRemoteRequests = False
    Set app = Application
End Sub


Private Sub UserForm_Terminate()
    Application.IgnoreRemoteRequests = bIgnoreRemoteRequests
End Sub


Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
    Call app_NewWorkbook(Wb)
End Sub


Private Sub app_NewWorkbook(ByVal Wb As Workbook)
        If Me.Tag <> "AllowOpening" Then
            Wb.Close SaveChanges:=False
            MsgBox "You cannot open or create a workbook at this time."
        End If
End Sub


[COLOR=#008000]'Example of a workbook that is allowed to be opened by your code.[/COLOR]
[COLOR=#008000]'Set the Tag value before the opening and reset afterwards.[/COLOR]
Private Sub CommandButton1_Click()
    Me.Tag = "AllowOpening"
        Workbooks.Open "C:\Users\Info-Hp\Desktop\Foo.xlsm"
    Me.Tag = ""
End Sub

The last CommandButton1_Click routine shows you a way of allowing the opening of a file from your userform via VBA while blocking the user.

Note:
As mikerickson pointed out, the code would fail (or rather be interrupted) if the user opens a macro-enabled workbook and is prompted with the Enable-Macros dialog.. The same if the opened workbook displays a modal window upon opening such as a MsgBox.

Edit:
Also, may I ask you, - Is it necessary for the userform to be modeless ? If you use a Modal userform, the user won't be able to open any workbooks via the excel interface.

 

[KeepTrying]

Dear Jaafar,
Thanks for you effort. As you pointed out code works except if....
Userform shouldn't be necessarily modeless. I changed it to Modal but it still allows to open e.g. .xlsm file (even if .xlsm file does not contain macro).
I start to run userform then open an .xslm file from Windows Explorer. After that I get usual message about "PERSONAL.XLSB is locked for editing" then .xlsm file opens.