Use VBA to change &File Control in Worksheet Menu Bar
 

I want to change the &File Control in the Worksheet Menu Bar. When the
workbook opens I want the code to list all the controls captions in the &File
Control that are visible onto a worksheet. I then want to set all the
controls listed visible property to False except these: &Save, Save &As...,
Print Pre&view, &Print, E&xit. This is what I have. I am getting an Error
indicated below.

Public Sub HideCommandBars(ByRef wbkGlobal As Workbook)

Dim rw As Long
Dim cbar As CommandBar
Dim ctrl As CommandBarControl
Dim colFileMenuControls As Collection

With wbkGlobal.Sheets("User Settings")
.Unprotect "AdTech"

' remove old data list and apply a header
.Cells.ClearContents
.Range("A1:C1").Value = Array("Command Bar Names", "Worksheet Menu
Control Captions", "Control Captions in File Menu")

' list all users visible command bars then hide them
rw = 2
For Each cbar In Application.CommandBars

' list visible tool bars, then hide all except "Worksheet Menu
Bar"
If cbar.Visible = True And cbar.Name < "Worksheet Menu Bar" Then
.Cells(rw, "A") = cbar.Name
cbar.Visible = False
rw = rw + 1
End If
Next cbar

' list all the menu controls in Worksheet Menu Bar, then hide all
except &File
rw = 2
For Each ctrl In Application.CommandBars("Worksheet Menu
Bar").Controls
If ctrl.Visible = True And ctrl.Caption < "&File" Then
.Cells(rw, "B") = ctrl.Caption
ctrl.Visible = False
rw = rw + 1
End If
Next ctrl

' list all contols in "&File" control
rw = 2
For Each ctrl In Application.CommandBars("Worksheet Menu
Bar").Controls("&File").Controls
If ctrl.Visible = True Then
.Cells(rw, "C") = ctrl.Caption
On Error Resume Next
ctrl.Visible = False
On Error GoTo 0
rw = rw + 1
End If
Next ctrl

.Protect "AdTech"
End With

Set colFileMenuControls = New Collection
With colFileMenuControls
.Add "&Save..."
.Add "Save &As..."
.Add "Print Pre&view"
.Add "&Print..."
.Add "E&xit"
End With

For Each ctrl In colFileMenuControls
ERROR Application.CommandBars("Worksheet Menu
Bar").Controls("&File").Add (Controls(ctrl))
Next ctrl

End Sub

--
Cheers,
Ryan