Prevent VBA editor from being opened

[Michael M]

Hi All
A colleague that I have written a workbook for has an issue with one of the users trying to open the VBA editor and see the code.
So it got me to thinking....is there a way to close the workbook if the VBE is opened.
I was thinking along the lines of when the press ALT + F11 a msgbox tells them they are unauthorised to view this page, and then closes the workbook without saving.

I haven't seen this done anywhere before, so it is a curiosity question at the moment.
The project in question does have a VBA password at the moment, so it's not a major problem yet !!

Any suggestions / inputs appreciated.

 

[biovio]

To Jaafar Tribak. I appreciate many of your posts and code. Regarding your code here, can you also make it Prevent the View Macros (Alt + F8) window from being opened? I need this as the user can load another (his) workbook in the same Excel instance and from this window to run his macros. In this way i hope to completely stop the user to run his macros from his workbook as long as my workbook and his are in the same instance of excel.

 

[Jaafar Tribak]

To Jaafar Tribak. I appreciate many of your posts and code. Regarding your code here, can you also make it Prevent the View Macros (Alt + F8) window from being opened? I need this as the user can load another (his) workbook in the same Excel instance and from this window to run his macros. In this way i hope to completely stop the user to run his macros from his workbook as long as my workbook and his are in the same instance of excel.

The only workaraound I can think of is to disable the Macros button that is located in the Developpers tab by modifying the Ribbon XML. You can make this dynamic so the Macros Button is only disbaled when the user's workbook is loaded and enabled back when closed. You can preferably do this in an add in or personal workbook.
I am ot sure if ALT+F8 will be disabled along with the Macros button after miodifying the ribbon xml ... If it is not disabled then you can simply disbale it with Application.OnKey "%{F8}", "" You can apply this dynamically as well.

 

[biovio]

The only workaraound I can think of is to disable the Macros button that is located in the Developpers tab by modifying the Ribbon XML. You can make this dynamic so the Macros Button is only disbaled when the user's workbook is loaded and enabled back when closed. You can preferably do this in an add in or personal workbook.
I am ot sure if ALT+F8 will be disabled along with the Macros button after miodifying the ribbon xml ... If it is not disabled then you can simply disbale it with Application.OnKey "%{F8}", "" You can apply this dynamically as well.

To Jaafar Tribak . Yet your code from above, block the Vbe window to to appear regardless if i press the button or use Alt+F11 . I tried to change your code for Alt+ F8 and also the View Macro button from the developer tab but without success. I end up only to block the same Vbe window. Here is my modified useless code :

Public Sub Hide_VBA_run(): Application.SendKeys "%{F8}": lCBTHook = SetWindowsHookEx(WH_CBT, AddressOf CBTProc, GetAppInstance, GetCurrentThreadId): End Sub

I guess i need different values or different approach for SetWindowsHookEx, but that is beyond my knowledge . I am glad that at least i can understand a portion of your code .
Please can you modify that portion of your code in order to prevent the View Macros (Alt + F8) window from being opened like you had done in your code from above . Thank you again for your great work .

 

[Jaafar Tribak]

@biovio

your code from above, block the Vbe window to to appear regardless if i press the button or use Alt+F11 . I tried to change your code for Alt+ F8 and also the View Macro button from the developer tab but without success

The hooking approach I took in post#3 won't work for not displaying the Macros dialog ... To be more specific, it could work but the code would need to be placed in a dll. This would need a lot of work.
As I said, the best (easier and more stable) approach is to modify the ribbon xml ... Look in the link below for how to modify the ribbon:

VBA Ribbon - Editor

VBA Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

bettersolutions.com

Once the xml ribbon is modified, the Macros button in the developper tab will remain disabled hence will prevent the user from displaying the Macros dialog.

Also, to just block ALT+F8, simply run the following line :

Application.OnKey "%{F8}", ""

Hopefully, you can do the above without much effort. If you have any issues let us know.