Regards CREATE MENUS with VBA - H E L P

leshem

New Member
Joined
Sep 23, 2002
Messages
49
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??
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
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

New Member
Joined
Sep 23, 2002
Messages
49
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)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
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

New Member
Joined
Sep 23, 2002
Messages
49
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
 

Forum statistics

Threads
1,144,280
Messages
5,723,470
Members
422,499
Latest member
think say

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