And here is a set of procedures to place in a module which could be named as (
mEnvironment ).
It also allows you to protect the environment of your workbook, from accessing
the sensitive data in it.
By calling the respective procedures in the proper places within your workbook.
Option Explicit
'===============================================================================\
================================
'-Environment Settings EndUser set initially.
'===============================================================================\
================================
Public StdCMDBar As Integer, FrmCMDBar As Integer
Public DspSTABAR As Integer, DspWINTBar As Integer, DspFRMLBar As Integer
'//=====================================================================
'// 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 M_ENVRN(Restore As Integer)
If Restore = 1 Then
'====================================
'Reset previous Environment Settings
'====================================
On Error Resume Next
Application.CommandBars("Standard").Visible = StdCMDBar
Application.CommandBars("Formatting").Visible = FrmCMDBar
Application.DisplayStatusBar = DspSTABAR
Application.DisplayFormulaBar = DspFRMLBar
Application.CommandBars("Worksheet Menu Bar").Reset
Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Visible = True
'======================================
'-Find out what Version MSOffice
'-is installed.
'======================================
If Int(Val(Application.Version)) > 8 Then
'Excel 2000 and the later version
Application.ShowWindowsInTaskbar = DspWINTBar
Else
'Excel 97
'==============================================
'-Not valid for Office97
'Application.ShowWindowsInTaskbar = DspWINTBar
'==============================================
End If
Err.Clear
'==========================================
'-This setting is only when to not show
'-other new workbooks to be displayed
'-within the application, to not be
'-displayed on the taskbar.
'(Application.ShowWindowsInTaskbar = False)
'(Application.ShowWindowsInTaskbar = True)
'==========================================
Else
On Error Resume Next
'====================================
'Save current Environment Settings
'====================================
StdCMDBar = Application.CommandBars("Standard").Visible
FrmCMDBar = Application.CommandBars("Formatting").Visible
DspSTABAR = Application.DisplayStatusBar
'===============================================
'-Not valid for Office97
'DspWINTBar = Application.ShowWindowsInTaskbar
'===============================================
DspFRMLBar = Application.DisplayFormulaBar
'======================================
'-Find out what Version MSOffice
'-is installed.
'======================================
If Int(Val(Application.Version)) > 8 Then
'Excel 2000 and the later version
DspWINTBar = Application.ShowWindowsInTaskbar
Else
'Excel 97
'==============================================
'-Not valid for Office97
'DspWINTBar = Application.ShowWindowsInTaskbar
'==============================================
End If
Err.Clear
'==========================================
'-This setting is only when to not show
'-other new workbooks to be displayed
'-within the application, to not be
'-displayed on the taskbar.
'(Application.ShowWindowsInTaskbar = False)
'(Application.ShowWindowsInTaskbar = True)
'==========================================
End If
End Sub
Function M_CmdBarReset(MainSheet As String, Reset As Integer, SheetName As
String)
On Error Resume Next
If SheetName <> MainSheet Then ActiveWorkbook.Sheets(SheetName).Visible =
False
Sheets(SheetName).Activate
If Reset = 0 Then
Application.CommandBars("Chart Menu Bar").Reset
Application.CommandBars("Chart Menu Bar").Visible = True
Application.CommandBars("Chart Menu Bar").Enabled = True
Application.CommandBars("Chart Menu Bar").Controls("Tools").Enabled =
True
Application.CommandBars("Chart").Visible = False
Application.ActiveChart.ProtectSelection = False
Application.ActiveChart.ProtectFormatting = False
Application.ActiveChart.ProtectData = False
Application.ActiveChart.Unprotect
Application.CommandBars.ActiveMenuBar.Reset
Application.CommandBars.ActiveMenuBar.Visible = True
Application.CommandBars.ActiveMenuBar.Enabled = True
Application.CommandBars("Worksheet Menu Bar").Reset
Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Visible = True
Application.CommandBars("Chart Menu Bar").Protection = msoBarNoCustomize
Else
Application.CommandBars("Chart Menu Bar").Reset
Application.CommandBars("Chart Menu Bar").Visible = False
Application.CommandBars("Chart Menu Bar").Enabled = False
Application.CommandBars("Chart Menu Bar").Controls("Tools").Enabled =
False
Application.CommandBars("Chart").Visible = False
Application.ActiveChart.ProtectSelection = True
Application.ActiveChart.ProtectFormatting = True
Application.ActiveChart.ProtectData = True
Application.ActiveChart.Protect
Application.CommandBars.ActiveMenuBar.Reset
Application.CommandBars.ActiveMenuBar.Visible = False
Application.CommandBars.ActiveMenuBar.Enabled = False
Application.CommandBars("Worksheet Menu Bar").Reset
Application.CommandBars("Worksheet Menu Bar").Enabled = False
Application.CommandBars("Worksheet Menu Bar").Visible = False
Application.CommandBars("Chart Menu Bar").Protection = msoBarNoCustomize
Application.CommandBars("Toolbar List").Enabled = False
End If
Err.Clear
End Function
Sub Dummy()
'// NoGo, leave blank OR Display a message eg.
10 'MsgBox "Sorry this command is NOT available", vbCritical
End Sub
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
'===============================================================================\
=================================
'-The following code is useful if you don't want a user to be able to edit the
'-Excel's main menu/commandbar by right clicking on it.
'===============================================================================\
=================================
'Purpose : Enables or disables Excel's main menu commandbar
'Inputs : [bEnable] If True enables the Excel's right click
menu, else disables it
'Outputs : N/A
'Notes :
'===============================================================================\
================================================
Function CommandBarRightClick(Optional bEnable As Boolean = False) As Boolean
On Error GoTo ErrFailed
CommandBars("toolbar List").Enabled = bEnable
CommandBarRightClick = True
Exit Function
ErrFailed:
Debug.Print "Error in CommandBarRightClick: " & Err.Description
CommandBarRightClick = False
End Function