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

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,558
Office Version
2016
Platform
Windows
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

Active Member
Joined
Aug 19, 2012
Messages
263
Office Version
365, 2010
Platform
Windows
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

Well-known Member
Joined
Dec 5, 2002
Messages
7,558
Office Version
2016
Platform
Windows
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 :
Code:
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.
 
Last edited:

KeepTrying

Active Member
Joined
Aug 19, 2012
Messages
263
Office Version
365, 2010
Platform
Windows
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.
 

Forum statistics

Threads
1,085,276
Messages
5,382,672
Members
401,798
Latest member
klikeras

Some videos you may like

This Week's Hot Topics

Top