Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet change to control worksheet visibility | Excel Programming | |||
Control item PRINT in menu FILE of Excel | Excel Discussion (Misc queries) | |||
worksheet image control appearance change (and back) with mousemov | Excel Programming | |||
How do I change the order of choices in menu File- Send To | Excel Worksheet Functions | |||
Help! I Deleted my File Menu bar Control | Excel Programming |