How to prevent the user from using the VBE ?!

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,253
Office Version
  1. 2016
Platform
  1. Windows
Can anyone think of a better way than disabling all the possible venues to the VBE namely disabling all the relevant key combinations and commandbar menues & controls ?

One limitation about the above approach is that the user ca always re-enable the relevant menus via the user interface or\and to recreate new custom commandbars\controls or simply add a control to a worksheet and double click it in order to easily access the VBE.

Note that locking a project from view is not what I am after . I don't want the user to be able to reach the VBE altogether.

Regards.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,253
Office Version
  1. 2016
Platform
  1. Windows
Thanks Krishna,

If I have a custom CommandBarButton to which a macro is assigned to activate the VBE then Ivan's code will fail to achieve its objective. Identifying which custom CommandBarButton(s) ,if any, has such a macro attached to it and disable such CommandBarButton(s) is not possible.

Another unlikely scenario (Getting paranoid here !) is to start a new instance of XL and run code to re-enable all shortcuts and commandbars from it via Automation.


Of course, the above scenarios are more than unlikely and who ever said that XL is 100% secure ! However, I am only raising this subject here from an educational perspective.

I am thinking about using a different approach namely Subclassing but am not quite there yet.


Regards.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,253
Office Version
  1. 2016
Platform
  1. Windows
Ok this disables the VBE using a different approach altogether. With this approach, there is no need to cycle through endless Shortcut keys or menus in order to disable them one by one.

Correct me if I am wrong but as far as I know, using this method, there is NO WAY to enable the VBE no matter what you do.

Unfortunately, because the code actually destroys the VBE window, I don't know how to restore it without first closing and restarting Excel. This is a real limitation .

Now, I don't think this is possible because it's not a seperate EXE but does anyone know if the VBE can somehow be launched from outside Excel ? ie without first starting Excel. That would solve the above limitation.


:eek: CLOSE ALL WORKBOOKS BEFORE YOU TRY THIS CODE JUST AS A PRECAUTION. :eek:


Here is a download example: http://www.savefile.com/files/174079


Code:

In a Standard Module :

Code:
Option Explicit

Private Declare Function SetTimer Lib "user32" _
(ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long

Private Declare Function killtimer Lib "user32" Alias "KillTimer" _
(ByVal hwnd As Long, ByVal nIDEvent As Long) As Long

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long

Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function DestroyWindow Lib "user32" (ByVal hwnd As Long) As Long

Private Const VBE_Class = "wndclass_desked_gsk"
Private Const BM_CLICK = &HF5
Private bHookEnabled As Boolean
Private lTimerID As Long


Private Sub TimerCallBack()

    Dim lErrorMsgHwnd As Long
    Dim lStaticTextHwnd As Long
    Dim lOkButtonHwnd As Long

    '\check if vb unfriendly error message is on display
    lErrorMsgHwnd = FindWindow("#32770", vbNullString)
    lStaticTextHwnd = FindWindowEx(lErrorMsgHwnd, 0, "STATIC", "Out of memory")
    
    '\if so, close it and display our MsgBox to inform the user
    If lStaticTextHwnd Then
        Call StopTimer
        lOkButtonHwnd = FindWindowEx(lErrorMsgHwnd, 0, "BUTTON", vbNullString)
        SendMessage lOkButtonHwnd, BM_CLICK, 0, 0
        MsgBox "Sorry. the VBE is diasabled . ", vbExclamation
        Call StartTimer
    End If

End Sub


Private Sub StopTimer()

    killtimer 0, lTimerID

End Sub

Private Sub StartTimer()

    lTimerID = SetTimer(0, 0, 1, AddressOf TimerCallBack)

End Sub


Run this test routine :

Code:
Sub DisableVBE()

    If Not bHookEnabled Then
        bHookEnabled = True
        StartTimer
        DestroyWindow FindWindow(VBE_Class, Application.VBE.MainWindow.Caption)
    End If

End Sub


Using a Timer alone without first destroying the VBE window doesn't give a smooth result ie lots of flickering and hangs the application.

Regards.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,253
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Has anyone given this a shot ?

Regards.
 

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
Has anyone given this a shot ?

Regards.

No, is that a challenge :LOL:

The most obvious one would be to disable Macros, that said there are
ways around this ... so I'll look @ this with enabling macros and get back..
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,253
Office Version
  1. 2016
Platform
  1. Windows
Ivan,

No, is that a challenge :LOL:

Being an admirer of your website and in keeping with the 'CAN DO' spirit , I can safely say YES - I do like a challenge :LOL:

I don't like the idea of completely having to destroy the VBE window just to prevent the user from activating it .I am experimenting with & refining some interesting code that Does overcome the above limitation !

I am off to work right now so I'll post it later.

Regards.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,253
Office Version
  1. 2016
Platform
  1. Windows
Ok. I have tested the following code in XL2002 and works well and doesn't seem to affect performance or cause any screen flickering despite using a timer.

The advantage of this approach over the satandard method of disabling the relevant menus is that you now don't need to cycle thru and disable the menus, commandbuttons or keyboard shortcuts that activate the VBE.- (Even user defined ones become disabled which is something that is impossible to do with the latter method !)

While the VBE is disabled, one can still record and run existing Macros, open workbooks as well as restore the VBE thus overcoming all the limitations of the above code I posted on 19/10. Entering Design Mode or embeeding Controls on the worksheets doesn't activate the VBE either.

Note that the code uses the Registry to temporaly store module level variables. This is necessary because the variables go out of scope if the user enters design mode or an error occurs.

Here is a download example : http://www.savefile.com/files/186729


Code to be placed in a Class Module named ClssVBEDisabler

Code:
Option Explicit

Private Declare Function SetTimer Lib "user32" _
(ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long

Private Declare Function killtimer Lib "user32" Alias "KillTimer" _
(ByVal hwnd As Long, ByVal nIDEvent As Long) As Long

Private Declare Function CreateWindowEx Lib "user32" Alias "CreateWindowExA" _
(ByVal dwExStyle As Long, ByVal lpClassName As String, ByVal lpWindowName _
As String, ByVal dwStyle As Long, ByVal x As Long, ByVal y As Long, _
ByVal nWidth As Long, ByVal nHeight As Long, ByVal hWndParent As Long, _
ByVal hMenu As Long, ByVal hInstance As Long, lpParam As Any) As Long

Private Declare Function DestroyWindow Lib "user32" (ByVal hwnd As Long) As Long

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function SetParent Lib "user32" _
(ByVal hWndChild As Long, ByVal hWndNewParent As Long) As Long

Private lTimerID As Long


Public Sub Disable(Optional Password As Variant)

    Dim bVBEIsEnabled As Boolean
    Dim sRet As String
    
    '\ignore error in case reg value:'VBEIsDisabled' doesn't exist
    On Error Resume Next
    bVBEIsEnabled = CBool(GetSetting("VBEDisablerClass", "Values", "VBEIsDisabled"))
    On Error GoTo 0
    
    '\handle each password scenario
    Select Case True
        Case bVBEIsEnabled
            GoTo AlreadyDisabled
        Case IsMissing(Password)
            sRet = InputBox("Enter an optional password to enable the VBE.  ", _
            "Disabling the VBE. ")
            If StrPtr(sRet) = 0 Then
                GoTo DisableVBENow
            Else
                Password = sRet
            End If
        End Select
SavePassword:
    SaveSetting "VBEDisablerClass", "Values", "Password", CStr(Password)
    SaveSetting "VBEDisablerClass", "Values", "PasswordExists", CStr(True)
DisableVBENow:
    Call DisableVBE
    Exit Sub
AlreadyDisabled:
    MsgBox "The VBE is already disabled.  ", vbExclamation
Cancelled:

End Sub


Public Sub Enable(Optional Password As Variant)

    Dim bPasswordExists As Boolean
    Dim sSavedPassWord As String
    Dim sRet As String
    
    '\ignore error in case reg value: 'PasswordExists' doesn't exist
    On Error Resume Next
    bPasswordExists = CBool(GetSetting("VBEDisablerClass", "Values", "PasswordExists"))
    
    '\if password exists, retrieve it from the registry
    If bPasswordExists Then
        sSavedPassWord = GetSetting("VBEDisablerClass", "Values", "Password")
        
        '\compare the stored password with the entered password
        Select Case True
            Case IsMissing(Password)
                sRet = InputBox("Enter the password to enable the VBE. ", "Enabling the VBE. ")
                If StrPtr(sRet) = 0 Then
                    GoTo Cancelled
                ElseIf UCase(sRet) <> UCase(sSavedPassWord) Then
                    GoTo WrongPassword
                End If
            Case UCase(Password) <> UCase(sSavedPassWord)
                GoTo WrongPassword
            Case UCase(Password) = UCase(sSavedPassWord)
                GoTo EnableVBENow
        End Select
    End If
EnableVBENow:
    Call EnableVBE
    Exit Sub
WrongPassword:
    MsgBox "Wrong password. ", vbExclamation
Cancelled:
 
End Sub


Private Sub DisableVBE()

    Dim lBtnHwnd As Long
    Dim lVBEhwnd As Long
    Const VBE_Class = "wndclass_desked_gsk"
    
    '\create an invisible dummy window to parent the VBE window
    '\this is so the VBE remains hidden and won't be activated !
    lBtnHwnd = CreateWindowEx(0, "BUTTON", vbNullString, 0, _
    0, 0, 0, 0, 0, 0, 0, 0)
    
    '\save all these variables in the registry for later use.
    '\saving the variables in the registry instead of saving
    '\them in public variables is necessary because all variables
    '\go out of scope when trying to activate the VBE or
    '\when entering Design Mode by the user!!
    lVBEhwnd = FindWindow(VBE_Class, Application.VBE.MainWindow.Caption)
    SaveSetting "VBEDisablerClass", "Values", "VBEhwnd", lVBEhwnd
    SaveSetting "VBEDisablerClass", "Values", "BtnHwnd", lBtnHwnd
    SetParent lVBEhwnd, lBtnHwnd
    SaveSetting "VBEDisablerClass", "Values", "VBEIsDisabled", True
    VBA.AppActivate Application.Caption
    
    '\start a timer to display a message whenever the user
    '\VBE tries activating the VBE.
    Call StartVBEWatch
    SaveSetting "VBEDisablerClass", "Values", "TimerID", lTimerID

End Sub

Private Sub EnableVBE()

    '\cleanup
    Call StopVBEWatch
    SetParent Val(GetSetting("VBEDisablerClass", "Values", "VBEhwnd")), 0
    DestroyWindow Val(GetSetting("VBEDisablerClass", "Values", "BtnHwnd"))
    DeleteSetting "VBEDisablerClass"
    Application.VBE.MainWindow.Visible = True
    Application.VBE.MainWindow.Visible = False

End Sub

Private Sub StartVBEWatch()

    lTimerID = SetTimer(0, 0, 250, AddressOf TimerCallBack)

End Sub

Private Sub StopVBEWatch()

    killtimer 0, Val(GetSetting("VBEDisablerClass", "Values", "TimerID"))

End Sub


Code in a Standard Module :

Code:
Option Explicit
 
Declare Function GetActiveWindow Lib "user32" () As Long

Sub TimerCallBack()

    If GetActiveWindow = GetSetting("VBEDisablerClass", "Values", "VBEhwnd") Then
        If Application.WindowState = xlMaximized Then
            MsgBox "The VB Editor is disabled ! ", vbInformation
            VBA.AppActivate Application.Caption
        End If
    End If

End Sub



Here is a ClssVBEDisabler Class Test :

Code:
Option Explicit

Dim VBE As ClssVBEDisabler

Sub Disable_Test()
    
    Set VBE = New ClssVBEDisabler
    
    '\you can pass an optional password
    VBE.Disable

End Sub

Sub Enable_Test()

    Set VBE = New ClssVBEDisabler
    
    '\you can pass an optional password
    VBE.Enable

End Sub


Any feedback much appreciated.

Regards.
 

Forum statistics

Threads
1,136,349
Messages
5,675,243
Members
419,556
Latest member
rdecker12

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
Top