Problem creating a menu bar

[cookieboy66]

Hi

I'm trying to create a menu bar with a series of easy buttons to active templates stored on our server. I've copied some code from a KB item on this site but can't get it to run properly. It keeps coming up with a syntax error message. I tried declaring the myButton but it still came up with the same error. Can anyone help?

Also, where can I find a list of face ids?

Sub AddMenuBar()
'The Dim statements make the rest of the code easier to create.
Dim Mybar As CommandBar
Dim cmd As CommandBarPopup
Dim i As Integer
Dim A(23) As Variant
CustomizationContext = ActiveDocument.AttachedTemplate
On Error Resume Next
'This checks if the menu already exists. If it does, it does not create a new one.
'The ampersand (&) in the name of the menu underlines the letter that follows it to give it a keyboard command (Alt-m) as many menus have. CommandBars("Menu Bar").Controls("Te&mplates").Caption = "Te&mplates"
If Not Err.Number = 0 Then
'Note that the parts of the array are ("Title of menu option","Macro to Run", FaceID for toolbar button)
A(1) = Array("Print to PDF", "PDF", 92)
A(2) = Array("Insert Header/Footer", "Header_footer", 237)
A(3) = Array("Works Instruction", "Works_Instruction", 89)
A(4) = Array("Aerco quote", "Aerco_quote", 80)
A(5) = Array("AHU quote", "AHU_quote", 98)
A(6) = Array("Air cooled chiller quote", "Air_cooled_chiller_quote", 280)
A(7) = Array("Boiler quote", "Boiler_quote", 1363)
A(8) = Array("Cooling tower quote", "Cooling_Tower_quote", 2528)
A(9) = Array("Damper/louvre quote", "Damper_louvre_quote", 2526)
A(10) = Array("Diffuser quote", "Diffuser_quote", 159)
A(11) = Array("Duct quote", "Duct_quote", 6)
A(12) = Array("Expansion tank quote", "Expansion_tank_quote", 6)
A(13) = Array("Fan quote", "Fan_quote", 6)
A(14) = Array("FCU quote", "FCU_quote", 6)
A(15) = Array("Flue quote", "Flue_quote", 6)
A(16) = Array("Heat exchanger quote", "Heat_exchanger_quote", 6)
A(17) = Array("Humidifier quote", "Humidifier_quote", 6)
A(18) = Array("Plate heat exchanger quote", "Plate_heat_exch_quote", 6)
A(19) = Array("Silencer quote", "Silencer_quote", 6)
A(20) = Array("Reheat coil quote", "Reheat_coil_quote", 6)
A(21) = Array("VAV quote", "VAV_quote", 6)
A(22) = Array("Vessel quote", "Vessel_quote", 6)
A(23) = Array("Water cooled chiller quote", "Water_cooled_chiller_quote", 6)
With CommandBars("Menu Bar").Controls
.Add(Type:=msoControlPopup, Before:=11).Caption = "Te&mplates"
End With
For i = 1 To UBound(A)
With CommandBars("Menu Bar").Controls("Te&mplates").Controls
Set myButton =.Add(Type=msoControlButton)
With myButton
.Caption = A(i)(0)
.OnAction = A(i)(1)
.FaceId = A(i)(2)
End With
End With
Next i
Else
End If
End Sub

[chandansify]

Hi

I'm trying to create a menu bar with a series of easy buttons to active templates stored on our server. I've copied some code from a KB item on this site but can't get it to run properly. It keeps coming up with a syntax error message. I tried declaring the myButton but it still came up with the same error. Can anyone help?

Also, where can I find a list of face ids?

Sub AddMenuBar()
'The Dim statements make the rest of the code easier to create.
Dim Mybar As CommandBar
Dim cmd As CommandBarPopup
Dim i As Integer
Dim A(23) As Variant
CustomizationContext = ActiveDocument.AttachedTemplate
On Error Resume Next
'This checks if the menu already exists. If it does, it does not create a new one.
'The ampersand (&) in the name of the menu underlines the letter that follows it to give it a keyboard command (Alt-m) as many menus have. CommandBars("Menu Bar").Controls("Te&mplates").Caption = "Te&mplates"
If Not Err.Number = 0 Then
'Note that the parts of the array are ("Title of menu option","Macro to Run", FaceID for toolbar button)
A(1) = Array("Print to PDF", "PDF", 92)
A(2) = Array("Insert Header/Footer", "Header_footer", 237)
A(3) = Array("Works Instruction", "Works_Instruction", 89)
A(4) = Array("Aerco quote", "Aerco_quote", 80)
A(5) = Array("AHU quote", "AHU_quote", 98)
A(6) = Array("Air cooled chiller quote", "Air_cooled_chiller_quote", 280)
A(7) = Array("Boiler quote", "Boiler_quote", 1363)
A(8) = Array("Cooling tower quote", "Cooling_Tower_quote", 2528)
A(9) = Array("Damper/louvre quote", "Damper_louvre_quote", 2526)
A(10) = Array("Diffuser quote", "Diffuser_quote", 159)
A(11) = Array("Duct quote", "Duct_quote", 6)
A(12) = Array("Expansion tank quote", "Expansion_tank_quote", 6)
A(13) = Array("Fan quote", "Fan_quote", 6)
A(14) = Array("FCU quote", "FCU_quote", 6)
A(15) = Array("Flue quote", "Flue_quote", 6)
A(16) = Array("Heat exchanger quote", "Heat_exchanger_quote", 6)
A(17) = Array("Humidifier quote", "Humidifier_quote", 6)
A(18) = Array("Plate heat exchanger quote", "Plate_heat_exch_quote", 6)
A(19) = Array("Silencer quote", "Silencer_quote", 6)
A(20) = Array("Reheat coil quote", "Reheat_coil_quote", 6)
A(21) = Array("VAV quote", "VAV_quote", 6)
A(22) = Array("Vessel quote", "Vessel_quote", 6)
A(23) = Array("Water cooled chiller quote", "Water_cooled_chiller_quote", 6)
With CommandBars("Menu Bar").Controls
.Add(Type:=msoControlPopup, Before:=11).Caption = "Te&mplates"
End With
For i = 1 To UBound(A)
With CommandBars("Menu Bar").Controls("Te&mplates").Controls
Set myButton =.Add(Type=msoControlButton)
With myButton
.Caption = A(i)(0)
.OnAction = A(i)(1)
.FaceId = A(i)(2)
End With
End With
Next i
Else
End If
End Sub

Try this if it works for you.



Sub AddMenuBar()
'The Dim statements make the rest of the code easier to create.
Dim Mybar As CommandBar
Dim cmd As CommandBarPopup
Dim i As Integer
Dim A(23) As Variant
CustomizationContext = ActiveDocument
On Error Resume Next
CommandBars("Menu Bar").Controls("Te&mplates").Delete

'Note that the parts of the array are ("Title of menu option","Macro to Run", FaceID for toolbar button)
A(1) = Array("Print to PDF", "PDF", 92)
A(2) = Array("Insert Header/Footer", "Header_footer", 237)
A(3) = Array("Works Instruction", "Works_Instruction", 89)
A(4) = Array("Aerco quote", "Aerco_quote", 80)
A(5) = Array("AHU quote", "AHU_quote", 98)
A(6) = Array("Air cooled chiller quote", "Air_cooled_chiller_quote", 280)
A(7) = Array("Boiler quote", "Boiler_quote", 1363)
A(8) = Array("Cooling tower quote", "Cooling_Tower_quote", 2528)
A(9) = Array("Damper/louvre quote", "Damper_louvre_quote", 2526)
A(10) = Array("Diffuser quote", "Diffuser_quote", 159)
A(11) = Array("Duct quote", "Duct_quote", 6)
A(12) = Array("Expansion tank quote", "Expansion_tank_quote", 6)
A(13) = Array("Fan quote", "Fan_quote", 6)
A(14) = Array("FCU quote", "FCU_quote", 6)
A(15) = Array("Flue quote", "Flue_quote", 6)
A(16) = Array("Heat exchanger quote", "Heat_exchanger_quote", 6)
A(17) = Array("Humidifier quote", "Humidifier_quote", 6)
A(18) = Array("Plate heat exchanger quote", "Plate_heat_exch_quote", 6)
A(19) = Array("Silencer quote", "Silencer_quote", 6)
A(20) = Array("Reheat coil quote", "Reheat_coil_quote", 6)
A(21) = Array("VAV quote", "VAV_quote", 6)
A(22) = Array("Vessel quote", "Vessel_quote", 6)
A(23) = Array("Water cooled chiller quote", "Water_cooled_chiller_quote", 6)
With CommandBars("Menu Bar").Controls
.Add(Type:=msoControlPopup, Before:=11).Caption = "Te&mplates"
End With
For i = 1 To UBound(A)
With CommandBars("Menu Bar").Controls("Te&mplates").Controls
Set myButton = .Add(Type:=msoControlButton)
With myButton
.Caption = A(i)(0)
.OnAction = A(i)(1)
.FaceId = A(i)(2)
End With
End With
Next i

End Sub

[fumei]

I am curious, and would like to see if I can help, but because there is no use of underscore characters, the code window is stretched SIX times the width of my screen.

This is too much for me to bother scrolling (left/right).

[perhol]

I made the original code function.
This i what i did:

In the "Array" section i found the name of the macro that is supposed to be called by the menu ie Ozgur:
A(1) = Array("Suat's Signature " , " Ozgur " , 92) I then went further down to find the actual macro. It looks like this:
Sub Ozgur() I then marked and copyed the macroname Ozgur, and only that.

Then i went back to the "Array" section and marked all between "" (Ozgur with 2 spaces on both sides) and replaced it with the copyed macroname.

If i tried just to overwrite the spaces and the macroname it would not have fixed the error, but when copyed from the actual macroname and pasted in place it worked!

Wonder why?

[perhol]

By the way fumei, the original code is here:
http://vbaexpress.com/kb/getarticle.php?kb_id=14

[perhol]

cookieboy66

The webaddress for a a list of face ids is clearly stated with the original code as:
http://j-walk.com/ss/excel/tips/tip67.htm

[cookieboy66]

Thanks Chandinsify, I managed to get it working. I had to declare myButton to get it to work properly.:beerchug: