M
Matthew John Antoszkiw
Hello all,
Is there a way I can prevent a user accessing the VBA Editor?
Thanks.
Is there a way I can prevent a user accessing the VBA Editor?
Thanks.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
C
Colo
Hello Matthew,
Do you mean you would like to hide the code?
If so, you can set the password to the VBAproject from Tools > Propert
of the VBE project > see the second tab
Do you mean you would like to hide the code?
If so, you can set the password to the VBAproject from Tools > Propert
of the VBE project > see the second tab
G
Guest
Hi Matthew
You can do this via VBA code
------------------------------------------
Sub Prevent_VBA(
With Applicatio
.Commandbars("Tools").Controls("Macro").Enabled = Fals
.Commandbars("Visual Basic").FindControl(id=1695).Enabled = Fals
.OnKey "%{F11}", "
.VBE.MainWindow.Visible = Fals
------------------------------------------
This will disable the Tools-->Macro menu (where you can open VBA Editor from), nullify the VBE toolbar button (in case the user tries to add the button from the Tools-->Customize), nullify the Alt-F11 shortcut, and close the VBE if it's already open
Just remember to re-enable all these before you leave, or else you wouldn't have very happy users! =
SuperJas.
You can do this via VBA code
------------------------------------------
Sub Prevent_VBA(
With Applicatio
.Commandbars("Tools").Controls("Macro").Enabled = Fals
.Commandbars("Visual Basic").FindControl(id=1695).Enabled = Fals
.OnKey "%{F11}", "
.VBE.MainWindow.Visible = Fals
------------------------------------------
This will disable the Tools-->Macro menu (where you can open VBA Editor from), nullify the VBE toolbar button (in case the user tries to add the button from the Tools-->Customize), nullify the Alt-F11 shortcut, and close the VBE if it's already open
Just remember to re-enable all these before you leave, or else you wouldn't have very happy users! =
SuperJas.
I
Ivan F Moala
You will need to disable more then just that.....
Option Explicit
'//
'// Tested Excel2000
'// Run DisableGettingIntoVBE from an Event procedure
'// eg. Workbook_Open or Worksheet_Activate
'// THEN run EnableGettingIntoVBE from the Opp Event procedure
'// eg. Workbook_Close or Worksheet_Deactivate
'//=====================================================================
'// Note:
'// In order to Disable access into the VBA Editor
'// you must Disable ALL references to Access the code
'// eg Macros...dialog, View Code - available via RighClick on Sheet Tab
'// Record New Macro..., Design Mode as the User can put the
'// workbook in design mode then select a control & double click to
'// view code, right click top Document Area etc
'// Also you need to Disable the Custom Toolbar List AND
'// the fact that Double clicking ANY area of the commandbars will
'// also give you the Customize Toolbars Option Dialog.
'// The following Routine Takes care of this.
Const dCustomize As Double = 797
Const dVbEditor As Double = 1695
Const dMacros As Double = 186
Const dRecordNewMacro As Double = 184
Const dViewCode As Double = 1561
Const dDesignMode As Double = 1605
Const dAssignMacro As Double = 859
Sub DisableGettingIntoVBE()
Application.VBE.MainWindow.Visible = False '// Close ALL VBE Windows 1st!
CmdControl dCustomize, False '// Customize
CmdControl dVbEditor, False '// &Visual Basic Editor
CmdControl dMacros, False '// Macros...
CmdControl dRecordNewMacro, False '// Record New Macro...
CmdControl dViewCode, False '// View Code
CmdControl dDesignMode, False '// Design Mode
CmdControl dAssignMacro, False '// Assig&n Macro...
Application.OnDoubleClick = "Dummy"
Application.CommandBars("ToolBar List").Enabled = False
Application.OnKey "%{F11}", "Dummy"
End Sub
Sub EnableGettingIntoVBE()
CmdControl dCustomize, True '// Customize
CmdControl dVbEditor, True '// &Visual Basic Editor
CmdControl dMacros, True '// Macros...
CmdControl dRecordNewMacro, True '// Record New Macro...
CmdControl dViewCode, True '// View Code
CmdControl dDesignMode, True '// Design Mode
CmdControl dAssignMacro, True '// Assig&n Macro...
Application.OnDoubleClick = vbNullString
Application.CommandBars("ToolBar List").Enabled = True
Application.OnKey "%{F11}"
End Sub
Sub Dummy()
'// NoGo, leave blank OR Display a message eg.
MsgBox "Sorry this command is NOT available", vbCritical
End Sub
Sub CmdControl(Id As Integer, TF As Boolean)
Dim CBar As CommandBar
Dim C As CommandBarControl
On Error Resume Next
For Each CBar In Application.CommandBars
Set C = CBar.FindControl(Id:=Id, Recursive:=True)
If Not C Is Nothing Then C.Enabled = TF
Next
Set C = Nothing
End Sub
Option Explicit
'//
'// Tested Excel2000
'// Run DisableGettingIntoVBE from an Event procedure
'// eg. Workbook_Open or Worksheet_Activate
'// THEN run EnableGettingIntoVBE from the Opp Event procedure
'// eg. Workbook_Close or Worksheet_Deactivate
'//=====================================================================
'// Note:
'// In order to Disable access into the VBA Editor
'// you must Disable ALL references to Access the code
'// eg Macros...dialog, View Code - available via RighClick on Sheet Tab
'// Record New Macro..., Design Mode as the User can put the
'// workbook in design mode then select a control & double click to
'// view code, right click top Document Area etc
'// Also you need to Disable the Custom Toolbar List AND
'// the fact that Double clicking ANY area of the commandbars will
'// also give you the Customize Toolbars Option Dialog.
'// The following Routine Takes care of this.
Const dCustomize As Double = 797
Const dVbEditor As Double = 1695
Const dMacros As Double = 186
Const dRecordNewMacro As Double = 184
Const dViewCode As Double = 1561
Const dDesignMode As Double = 1605
Const dAssignMacro As Double = 859
Sub DisableGettingIntoVBE()
Application.VBE.MainWindow.Visible = False '// Close ALL VBE Windows 1st!
CmdControl dCustomize, False '// Customize
CmdControl dVbEditor, False '// &Visual Basic Editor
CmdControl dMacros, False '// Macros...
CmdControl dRecordNewMacro, False '// Record New Macro...
CmdControl dViewCode, False '// View Code
CmdControl dDesignMode, False '// Design Mode
CmdControl dAssignMacro, False '// Assig&n Macro...
Application.OnDoubleClick = "Dummy"
Application.CommandBars("ToolBar List").Enabled = False
Application.OnKey "%{F11}", "Dummy"
End Sub
Sub EnableGettingIntoVBE()
CmdControl dCustomize, True '// Customize
CmdControl dVbEditor, True '// &Visual Basic Editor
CmdControl dMacros, True '// Macros...
CmdControl dRecordNewMacro, True '// Record New Macro...
CmdControl dViewCode, True '// View Code
CmdControl dDesignMode, True '// Design Mode
CmdControl dAssignMacro, True '// Assig&n Macro...
Application.OnDoubleClick = vbNullString
Application.CommandBars("ToolBar List").Enabled = True
Application.OnKey "%{F11}"
End Sub
Sub Dummy()
'// NoGo, leave blank OR Display a message eg.
MsgBox "Sorry this command is NOT available", vbCritical
End Sub
Sub CmdControl(Id As Integer, TF As Boolean)
Dim CBar As CommandBar
Dim C As CommandBarControl
On Error Resume Next
For Each CBar In Application.CommandBars
Set C = CBar.FindControl(Id:=Id, Recursive:=True)
If Not C Is Nothing Then C.Enabled = TF
Next
Set C = Nothing
End Sub
M
Matthew John Antoszkiw
Thanks all. I'll look into all of these options.
button from the Tools-->Customize), nullify the Alt-F11 shortcut, and close
the VBE if it's already open.
from), nullify the VBE toolbar button (in case the user tries to add theIvan F Moala said:You will need to disable more then just that.....
Option Explicit
'//
'// Tested Excel2000
'// Run DisableGettingIntoVBE from an Event procedure
'// eg. Workbook_Open or Worksheet_Activate
'// THEN run EnableGettingIntoVBE from the Opp Event procedure
'// eg. Workbook_Close or Worksheet_Deactivate
'//=====================================================================
'// Note:
'// In order to Disable access into the VBA Editor
'// you must Disable ALL references to Access the code
'// eg Macros...dialog, View Code - available via RighClick on Sheet Tab
'// Record New Macro..., Design Mode as the User can put the
'// workbook in design mode then select a control & double click to
'// view code, right click top Document Area etc
'// Also you need to Disable the Custom Toolbar List AND
'// the fact that Double clicking ANY area of the commandbars will
'// also give you the Customize Toolbars Option Dialog.
'// The following Routine Takes care of this.
Const dCustomize As Double = 797
Const dVbEditor As Double = 1695
Const dMacros As Double = 186
Const dRecordNewMacro As Double = 184
Const dViewCode As Double = 1561
Const dDesignMode As Double = 1605
Const dAssignMacro As Double = 859
Sub DisableGettingIntoVBE()
Application.VBE.MainWindow.Visible = False '// Close ALL VBE Windows 1st!
CmdControl dCustomize, False '// Customize
CmdControl dVbEditor, False '// &Visual Basic Editor
CmdControl dMacros, False '// Macros...
CmdControl dRecordNewMacro, False '// Record New Macro...
CmdControl dViewCode, False '// View Code
CmdControl dDesignMode, False '// Design Mode
CmdControl dAssignMacro, False '// Assig&n Macro...
Application.OnDoubleClick = "Dummy"
Application.CommandBars("ToolBar List").Enabled = False
Application.OnKey "%{F11}", "Dummy"
End Sub
Sub EnableGettingIntoVBE()
CmdControl dCustomize, True '// Customize
CmdControl dVbEditor, True '// &Visual Basic Editor
CmdControl dMacros, True '// Macros...
CmdControl dRecordNewMacro, True '// Record New Macro...
CmdControl dViewCode, True '// View Code
CmdControl dDesignMode, True '// Design Mode
CmdControl dAssignMacro, True '// Assig&n Macro...
Application.OnDoubleClick = vbNullString
Application.CommandBars("ToolBar List").Enabled = True
Application.OnKey "%{F11}"
End Sub
Sub Dummy()
'// NoGo, leave blank OR Display a message eg.
MsgBox "Sorry this command is NOT available", vbCritical
End Sub
Sub CmdControl(Id As Integer, TF As Boolean)
Dim CBar As CommandBar
Dim C As CommandBarControl
On Error Resume Next
For Each CBar In Application.CommandBars
Set C = CBar.FindControl(Id:=Id, Recursive:=True)
If Not C Is Nothing Then C.Enabled = TF
Next
Set C = Nothing
End Sub
"SuperJas" <[email protected]> wrote in message
button from the Tools-->Customize), nullify the Alt-F11 shortcut, and close
the VBE if it's already open.