Prevent VBA editor from being opened

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,898
Office Version
  1. 365
  2. 2019
  3. 2013
  4. 2007
Platform
  1. Windows
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.
 
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.
 
Upvote 0

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.
 
Upvote 0
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 .
 
Upvote 0
@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:
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 :
VBA Code:
Application.OnKey "%{F8}", ""

Hopefully, you can do the above without much effort. If you have any issues let us know.
 
Last edited:
Upvote 0
To @Jaafar Tribak . Thank you for your help. In post#14, the "VBA Ribbon - Editor" uses "The Custom UI Editor" and:
1) "is now officially unsupported by Microsoft. more details"
and even worse
2) "Windows 11 - Not Compatible".
I hate the ribbon and i love the menu stile form Office XP, also I love the look and the embedded look-customization from windows 7. That is why
3) there are many like me that use windows 7 and Office XP because prior to Office 2007 there was no ribbon.
I had used tweekers to get rid of ribbons and get back menus in Office 2007 and i hope to find new one for Office 2019 or above.
Also:
4) "The Custom UI Editor"need to be installed on the user PC and
5) if "it" is there the user can undo that customization.
Sadly, only the hook approach in post#3 is appropriate. Maybe it only needs other value for
Private Const GWL_HINSTANCE As Long = (-6): Private Const WH_CBT As Long = 5: Private Const HCBT_ACTIVATE As Long = 5: Private lCBTHook As Long: Private lVBEhwnd As Long
or some change (other than F11 replaced with F8) in
Public Sub Hide_The_VBE_Window(): Application.SendKeys "%{F8}": lCBTHook = SetWindowsHookEx(WH_CBT, AddressOf CBTProc, GetAppInstance, GetCurrentThreadId): End Sub
 
Upvote 0
@Jaafar Tribak By simply inserting Sub Restore_The_VBE_Window before the end of Sub Hide_The_VBE_Window i found out that Sub Restore_The_VBE_Window does not work at all, but luckily it doesn't mater since it is in the before close sub and after reopening, excel restore his vbe functionality anyway. Also lVBEhwnd is 0 all the time.
On the other hand the whole idea can be overridden by not allowing macro at the workbook opening...
Therefore i will abandon this approach even if it is beautiful.
My greatest sympathy to @Jaafar Tribak and his great work.
 
Upvote 0
On the other hand the whole idea can be overridden by not allowing macro at the workbook opening...
Indeed. This whole thing assumes Macros are enabled. One could place the code in an addin and place the addin in a trusted location in which case its macros will run without security warnings or requiring manual enablement.
 
Upvote 0
Indeed. This whole thing assumes Macros are enabled. One could place the code in an addin and place the addin in a trusted location in which case its macros will run without security warnings or requiring manual enablement.
Yet even the addin file is visible and it can be opened with macro disabled...
 
Upvote 0

Forum statistics

Threads
1,226,329
Messages
6,190,321
Members
453,604
Latest member
ADJ2RGJ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top