Changing Toolbar and Menu Items

  • Thread starter Thread starter Maria
  • Start date Start date
M

Maria

I would appreciate if someone could help with the VBA code required to do
the following:
1) Hide all toolbars
2) In the menu display only the following: FILE (Save, Save as, Close,
Exit), TOOLS (Protection, Protect/Unprotect Worksheet),
3) I intend to reset before close to revert back to default

Thanks a lot

Maria .
 
Maria,

My read of your post is that:-
In the Tools menu, you want only to display the Protection
menu item; and subordinate to this, you want to only
display the Protect Sheet submenu item (i.e. you want to
hide the Protect Workbook and Protect and Share Workbook
submenu items).

Be advised, I'm a little queasy offering this because I've
seen this type of code cause a complication in the past on
a different system. However, in the instance I refer to,
it was easily resolved. It DOES work on my system but
I've only tested it a few times and the code has been
hastily written.

Best regards,

Greg
 
Oops... I forgot to paste in the code.

Regards,
Greg

Sub FormatCommandBars()
Dim CB As CommandBar, CaptArr As Variant, i As Integer
Dim Ctrl As CommandBarControl, Item As CommandBarControl
Dim SubItem As CommandBarControl, KeepVis As Boolean

On Error Resume Next
CaptArr = Array(3, 106, 748, 752)
With Application
For Each CB In .CommandBars
If CB.Type = msoBarTypeNormal Then CB.Visible = False
Next
For Each Ctrl In .CommandBars(1).Controls
If Ctrl.ID = 30002 Then
For Each Item In Ctrl.Controls
For i = 1 To 4
If Item.ID = CaptArr(i) Then KeepVis = True
Next
If KeepVis = False Then Item.Visible = False
KeepVis = False
Next
ElseIf Ctrl.ID = 30007 Then
For Each Item In Ctrl.Controls
If Item.ID = 30029 Then
For Each SubItem In Item.Controls
If SubItem.ID <> 893 Then SubItem.Visible = False
Next
Else
Item.Visible = False
End If
Next
Else
Ctrl.Visible = False
End If
Next
End With

End Sub
 
Back
Top