This code when created in excel 2003 added a menu bar to the bottom of the screen when using that workbook. With excel 2007, it no longer appears and is now in the add-ins menu. This is fine until a user has 2 or 3 files open with different menu bars, because now instead of being sheet specific, all of the menu bars appear below each other in the add-ins menu. This is causing some confusion with some users. Is there any way to get excel 2007 to "put" the menu bar back at the bottom of the specific workbook.
Any help appreciated
Rgds Nigel
Public Const ToolBarName As String = "MPG Toolbar"
'===========================================
Sub Auto_Open()
'''Call CreateMenubar
End Sub
'===========================================
Sub Auto_Close()
Call RemoveMenubar
End Sub
'===========================================
Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars("MPG Toolbar").Delete
On Error GoTo 0
End Sub
'===========================================
Sub CreateMenubar()
Dim iCtr As Long
Dim MacNames As Variant
Dim CapNamess As Variant
Dim TipText As Variant
Call RemoveMenubar
MacNames = Array("Button3", _
"Button4", _
"Button5", _
"Button6", _
"Button7", _
"Button8", _
"BY_REG_Button3")
CapNamess = Array("VIEW 01/02 TRUCKS", _
"VIEW 05 TRUCKS", _
"VIEW 06 TRUCKS", _
"VIEW 07 TRUCKS", _
"VIEW 08 TRUCKS", _
"VIEW INDIVIDUAL TRUCKS", _
"RETURN TO MAIN CHART")
TipText = Array("DATA COLLECTED FOR 01/02 TRUCKS", _
"DATA COLLECTED FOR 05 TRUCKS", _
"DATA COLLECTED FOR 06 TRUCKS", _
"DATA COLLECTED FOR 07 TRUCKS", _
"DATA COLLECTED FOR 08 TRUCKS", _
"VIEW A TRUCK SEPERATELY", _
"RETURN")
With Application.CommandBars.Add
.Name = ToolBarName
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarBottom
For iCtr = LBound(MacNames) To UBound(MacNames)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
.Caption = CapNamess(iCtr)
.Style = msoButtonIconAndCaption
.FaceId = 71 + iCtr
.TooltipText = TipText(iCtr)
End With
Next iCtr
End With
End Sub
Bookmarks