Custom Menu Add Problem
 

I have this code in ThisWorkbook in an add in .xla

Private Sub Workbook_Open()
Dim cbWsMenuBar As CommandBar
Dim TrCustom As CommandBarControl
Dim iHelpIndex As Integer

Set cbWsMenuBar = Application.CommandBars("Worksheet Menu Bar")
iHelpIndex = cbWsMenuBar.Controls("Help").Index
Set TrCustom = cbWsMenuBar.Controls.Add(Type:=msoControlPopup,
befo=iHelpIndex)
With TrCustom
.Caption = "Translate"

With .Controls.Add(Type:=msoControlButton)
.Caption = "Refresh Branch List"
.OnAction = "'ERAC_Universal_Translate7x.xla'!Group_Branch "
End With

With .Controls.Add(Type:=msoControlButton)
.Caption = "Refresh Account List"
.OnAction = "'ERAC_Universal_Translate7x.xla'!AccountList"
End With

End With

When I open Excel I briefly see the new menu item on the menu bar but is
disappears when Excel automatically adds a new workbook "Book1". If I run
the private sub after everything is open the menu is added and it works. But
when I close and reopen Excel it doesn't persist. Any idea where I should
look to figure out what is wiping this out?

Thanks,
John

Custom Menu Add Problem
 

I'd look for a file named *.xlb.

Check to see if you have an extra one in your XLStart (any of them!) folder.

John Hutcins wrote:

I have this code in ThisWorkbook in an add in .xla

Private Sub Workbook_Open()
Dim cbWsMenuBar As CommandBar
Dim TrCustom As CommandBarControl
Dim iHelpIndex As Integer

Set cbWsMenuBar = Application.CommandBars("Worksheet Menu Bar")
iHelpIndex = cbWsMenuBar.Controls("Help").Index
Set TrCustom = cbWsMenuBar.Controls.Add(Type:=msoControlPopup,
befo=iHelpIndex)
With TrCustom
.Caption = "Translate"

With .Controls.Add(Type:=msoControlButton)
.Caption = "Refresh Branch List"
.OnAction = "'ERAC_Universal_Translate7x.xla'!Group_Branch "
End With

With .Controls.Add(Type:=msoControlButton)
.Caption = "Refresh Account List"
.OnAction = "'ERAC_Universal_Translate7x.xla'!AccountList"
End With

End With

When I open Excel I briefly see the new menu item on the menu bar but is
disappears when Excel automatically adds a new workbook "Book1". If I run
the private sub after everything is open the menu is added and it works. But
when I close and reopen Excel it doesn't persist. Any idea where I should
look to figure out what is wiping this out?

Thanks,
John

--

Dave Peterson

Custom Menu Add Problem
 

Thanks, that did it!
John

"Dave Peterson" wrote:

I'd look for a file named *.xlb.

Check to see if you have an extra one in your XLStart (any of them!) folder.

John Hutcins wrote:

I have this code in ThisWorkbook in an add in .xla

Private Sub Workbook_Open()
Dim cbWsMenuBar As CommandBar
Dim TrCustom As CommandBarControl
Dim iHelpIndex As Integer

Set cbWsMenuBar = Application.CommandBars("Worksheet Menu Bar")
iHelpIndex = cbWsMenuBar.Controls("Help").Index
Set TrCustom = cbWsMenuBar.Controls.Add(Type:=msoControlPopup,
befo=iHelpIndex)
With TrCustom
.Caption = "Translate"

With .Controls.Add(Type:=msoControlButton)
.Caption = "Refresh Branch List"
.OnAction = "'ERAC_Universal_Translate7x.xla'!Group_Branch "
End With

With .Controls.Add(Type:=msoControlButton)
.Caption = "Refresh Account List"
.OnAction = "'ERAC_Universal_Translate7x.xla'!AccountList"
End With

End With

When I open Excel I briefly see the new menu item on the menu bar but is
disappears when Excel automatically adds a new workbook "Book1". If I run
the private sub after everything is open the menu is added and it works. But
when I close and reopen Excel it doesn't persist. Any idea where I should
look to figure out what is wiping this out?

Thanks,
John

--

Dave Peterson