COMMAND BAR - need to change a personal menu bar

[itr674]

I had someone build a menu bar for me. I now want to change and delete a couple of buttons on it.

When go to the Customize menu and select a button to delete it comes back when I reopen the file?

I have commented out a couple of VBA lines, such as ".protection = msoBarNoChangeVisible" and "Application.CommandBars("Toolbar List").Enabled = False." I don't think these lines are the problem but don't know VBA enough to know ...
This message was edited by em on 2002-10-18 11:36
This message was edited by em on 2003-02-09 17:20

 

[jimbojones]

I would guess that the procedure built for you creates the menu bar when the spreadsheet opens. Go into VBA Editor (Alt-F11) and find the "This Workbook" Object in here there should be a statement something along the lines of:

Application.Commandbars("Personal Menu Bar
Name").Add

Then something like:

Application.Commandbars("Personal Menu Bar Name").controls.Add(msocontrolbutton)
followed by various caption and formatting details. This should be what you need to change.

Good luck and hope this helps.

Jim

 

[itr674]

Jim -- yea I thought it would be in the VBE but I can't find any lines where it is being added. Below is code from the "OnAction" macro and the answer probably is in here some where, maybe the "strName" or "strActionName"--I just haven't figured it out yet ??

Sub GetAllCL()
On Error Resume Next

Set cbMenu = Application.CommandBars("EM").Controls("Cover Letters")

'Delete existing ones.
For Each cControl In cbMenu.CommandBar.Controls
cControl.Delete
Next

strActionName = ThisWorkbook.Name & "!GoToCLSheet"

'then add all meeting criteria
For Each wSheet In Worksheets
If Right(wSheet.Name, 2) = "CL" Then
'Remove the suffix
strName = WorksheetFunction.Substitute(UCase(wSheet.Name), " - CL", "")
cbMenu.Controls.Add().Caption = strName
cbMenu.Controls(strName).OnAction = strActionName
End If
Next

cbMenu.Controls.Add().Caption = Sheet32.Name
strActionName = ThisWorkbook.Name & "!GoToIntro"
cbMenu.Controls(Sheet32.Name).OnAction = strActionName
Run "KillVariables"
On Error GoTo 0
End Sub