Regards CREATE MENUS with VBA - H E L P

[leshem]

Hi all,
I disabled all the standard menues and left as is the "Help" with the followed code :
With .Menus
.Add Caption:="&Help", restore:=True
End With
my Question: I would like to use from the "Insert" menu, only the function "Charts" as is and disable all the rest.
ca someone tell me how??

 

[Richie(UK)]

Hi leshem,

Once you have restored the Insert menu, try the following:

<pre>
Sub DisableInsertChoices()
Dim Item As CommandBarControl

For Each Item In Application.CommandBars("Insert").Controls
If Item.Caption <> "C&hart..." Then Item.Enabled = False 'leave Chart option
Next

End Sub

Sub EnableInsertChoices()
Dim Item As CommandBarControl

For Each Item In Application.CommandBars("Insert").Controls
Item.Enabled = True
Next

End Sub</pre>

HTH

 

[leshem]

Hi Richie,
thanks for your answer.
but It wont let me restore the Insert menu the way I did for the Help Menu, and what is the syntax to call the function you wrote?

 

[Richie(UK)]

Hi leshem,

What code did you use to hide the menus?

See if this works:
Application.CommandBars("Insert").Enabled = True

The routines above are just plain macros called or run however you wish, eg Workbook events, form button, hot keys. Is that what you mean?

 

[leshem]

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