Hi all,

Can anyone tell me the problem here? I'm adding a toolbar that does various
things that are useful to me. It works fine right up to the
msoContolButtonDropDown, upon trying to add which I get error 5, invalid
procedure call/argument. Does the dropdown button not work properly? Or,
more likely, am I missing something?

Cheers,

Jeff

With CommandBars.Add(Name:="Jaffa's Toolbar")
With .Controls.Add(Type:=msoControlButton)
.OnAction = "GenFooter"
.FaceId = 29
.TooltipText = "Add Footer"
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Nofooter"
.FaceId = 3265
.TooltipText = "Remove Footer"
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Footeropt"
.FaceId = 5598
.TooltipText = "Footer Options"
End With
With .Controls.Add(msoControlComboBox)
.OnAction = "SheetSel"
.Tag = "cmdSheetSel"
End With
With .Controls.Add(msoControlButton)
.OnAction = "RowSel"
.FaceId = 7008
.TooltipText = "Select whole rows"
End With
With .Controls.Add(msoControlButton)
.OnAction = "ColSel"
.FaceId = 7009
.TooltipText = "Select whole columns"
End With
With .Controls.Add(msoControlButton)
.OnAction = "RangCalc"
.TooltipText = "Recalculate selection"
.FaceId = 1952
End With
With .Controls.Add(msoControlButton)
.OnAction = "CopyFolderLink"
.TooltipText = "Copy folder tree"
.FaceId = 1667
End With
With .Controls.Add(msoControlButtonDropdown)
.Tag = "cmdCalcChange"
Select Case Application.Calculation
Case xlCalculationAutomatic
.FaceId = 6735
Case xlCalculationManual
.FaceId = 6743
Case xlCalculationSemiautomatic
.FaceId = 6751
End Select
With .AddItem
.OnAction = "CalcChangeM"
.TooltipText = "Manual calculation"
.FaceId = 6743
End With
With .AddItem
.OnAction = "CalcChangeS"
.TooltipText = "Semiautomatic calculation"
.FaceId = 6751
End With
With .AddItem
.OnAction = "CalcChangeA"
.TooltipText = "Automatic calculation"
.FaceId = 6735
End With
End With
End With

Jeff,

While that is a valid type of commandbutton, not all types are available to
the user for cutom commandbars. IIRC, only five of the many types are
available to the end user. The other types are for built-in controls, so
the constants are there because those types return those values when reading
the properties of the built in controls.

HTH,
Bernie
MS Excel MVP

"Jeff Standen" wrote in message
...
Hi all,

Can anyone tell me the problem here? I'm adding a toolbar that does
various
things that are useful to me. It works fine right up to the
msoContolButtonDropDown, upon trying to add which I get error 5, invalid
procedure call/argument. Does the dropdown button not work properly? Or,
more likely, am I missing something?

Cheers,

Jeff

With CommandBars.Add(Name:="Jaffa's Toolbar")
With .Controls.Add(Type:=msoControlButton)
.OnAction = "GenFooter"
.FaceId = 29
.TooltipText = "Add Footer"
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Nofooter"
.FaceId = 3265
.TooltipText = "Remove Footer"
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Footeropt"
.FaceId = 5598
.TooltipText = "Footer Options"
End With
With .Controls.Add(msoControlComboBox)
.OnAction = "SheetSel"
.Tag = "cmdSheetSel"
End With
With .Controls.Add(msoControlButton)
.OnAction = "RowSel"
.FaceId = 7008
.TooltipText = "Select whole rows"
End With
With .Controls.Add(msoControlButton)
.OnAction = "ColSel"
.FaceId = 7009
.TooltipText = "Select whole columns"
End With
With .Controls.Add(msoControlButton)
.OnAction = "RangCalc"
.TooltipText = "Recalculate selection"
.FaceId = 1952
End With
With .Controls.Add(msoControlButton)
.OnAction = "CopyFolderLink"
.TooltipText = "Copy folder tree"
.FaceId = 1667
End With
With .Controls.Add(msoControlButtonDropdown)
.Tag = "cmdCalcChange"
Select Case Application.Calculation
Case xlCalculationAutomatic
.FaceId = 6735
Case xlCalculationManual
.FaceId = 6743
Case xlCalculationSemiautomatic
.FaceId = 6751
End Select
With .AddItem
.OnAction = "CalcChangeM"
.TooltipText = "Manual calculation"
.FaceId = 6743
End With
With .AddItem
.OnAction = "CalcChangeS"
.TooltipText = "Semiautomatic calculation"
.FaceId = 6751
End With
With .AddItem
.OnAction = "CalcChangeA"
.TooltipText = "Automatic calculation"
.FaceId = 6735
End With
End With
End With