Richie : my code suppose to disable and delete all the toolbars and menus when the application is on. Ive created my own menus which are designed to let the user to use only the specific few options he should use. one of them is the chart option.
the function below is the Create menus which is operated when the workbook is open:
Sub CreateMenu()
Dim Item As CommandBarControl
Application.ScreenUpdating = False
Application.CellDragAndDrop = False
For Each tb In Toolbars
If tb.Visible = True Then
tb.Visible = False
End If
Next
With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With
With Application
.OnKey "^x", ""
.OnKey "^v", ""
.OnKey "%{TAB}", ""
.OnKey "^{TAB}", ""
.OnKey "{ENTER}", ""
.OnKey "~", ""
.OnKey "^{PGUP}", ""
.OnKey "^{PGDN}", ""
.OnKey "^{+}", ""
.OnKey "%{+}", ""
For i = 2 To 12
.OnKey "{F" & i & "}", ""
.OnKey "^{F" & i & "}", ""
.OnKey "%{F" & i & "}", ""
.OnKey "+{F" & i & "}", ""
Next i
For i = 0 To 9
.OnKey "^" & i, ""
.OnKey "%" & i, ""
Next i
.OnKey "%=", ""
.OnKey "%'", ""
'.OnKey "^s", ""
.OnKey "^b", ""
.OnKey "^n", ""
.OnKey "^'", ""
.OnKey "^;", ""
.OnKey "^h", ""
.OnKey "^g", ""
.OnKey "^f", ""
.OnKey "^o", ""
.OnKey "^`", ""
.OnKey "^+;", ""
.OnKey "%^{F2}", ""
.OnKey "+%{F2}", ""
.OnDoubleClick = "DoubleClick"
End With
On Error Resume Next
With MenuBars(xlWorksheet)
For Each mn In .Menus
mn.Delete
Next
With .Menus
.Add Caption:="&Help", restore:=True
.Add Caption:="&Insert", restore:=True
.Add Caption:="&Tools", before:=1
.Add Caption:="&View", before:=1
.Add Caption:="&Edit", before:=1
.Add Caption:="&File", before:=1
End With
For Each Item In Application.CommandBars("Insert").Controls
If Item.Caption <> "C&hart..." Then Item.Enabled = False 'leave Chart option
Next
With .Menus("&File").MenuItems
.Add Caption:="&Close", OnAction:="SpecialClose"
.Add Caption:="-"
.Add Caption:="&Print", OnAction:="SpecialPrint"
.Add Caption:="-"
.Add Caption:="E&xit", OnAction:="SpecialExit"
.Add Caption:="-"
'.Add Caption:="&Save Ctrl+S", OnAction:="SpecialSave"
.Add Caption:="Save &As", OnAction:="SpecialSaveAs"
End With
With .Menus("&Edit").MenuItems
.Add Caption:="&Copy", OnAction:="SpecialCopy"
.Add Caption:="&Paste", OnAction:="SpecialPaste"
.Add Caption:="Cle&ar Contents", OnAction:="SpecialClearContents"
End With
With .Menus("&View").MenuItems
.Add Caption:="&Zoom", OnAction:="SpecialZoom"
End With
With .Menus("&Tools").MenuItems
.Add Caption:="&Data Analysis", OnAction:="SpecialAnalysis"
.Add Caption:="Add Row", OnAction:="insert_row"
'************************************************************
.Add Caption:="Unprotect", OnAction:="Unprotect"
'************************************************************
End With
End With
End Sub
Can you help me with it? the Insert menu is not created somehow on runtime. I couldnt figure why.
10X 10X 10X