Excel 2003 - VBA

[Craig Brandt]

I am trying to create a floating toolbar on the fly with dropdown menus and
also first level buttons. I cannot figure out how to assign an icon to a
button. Here is a piece of the code where I would like to assign an icon to
the Calculate button. How do I assign the ICON picture?

Help would be appreciated.
Thanks
Craig


Public Sub FltgToolbar()

'
' Establishes a Floating Toolbar for this workbook only.

Dim RvwBar As Office.CommandBar

' Close previous Toolbar
On Error Resume Next
Application.CommandBars("Returns").Delete
With Application.CommandBars.Add
.Name = "Returns"
.Left = 300
.Top = 200
.Visible = True
.Position = msoBarFloating
' Download Drop Downs
With .Controls.Add(Type:=msoControlPopup)
.Caption = "Downloads"
.TooltipText = "Imports Data into this WorkBook"

With .Controls.Add(Type:=msoControlButton)
.OnAction = "DLToday"
.Caption = "Import Current"
End With

With .Controls.Add(Type:=msoControlButton)
.OnAction = "DLPrev"
.Caption = "Import Previous"
End With
End With

' Calc Button

With .Controls.Add(Type:=msoControlButton)
.Caption = "Calculate"
.OnAction = "Calculate"
.TooltipText = "Calculates returns based on current data"
End With
End With
Application.CommandBars("Returns").Controls.Add Type:=msoControlButton,
ID:=109
Application.CommandBars("Returns").Controls.Add Type:=msoControlButton,
ID:=4
Application.CommandBars("Returns").Controls.Add Type:=msoControlButton,
ID:=1849
Application.CommandBars("Returns").Controls.Add Type:=msoControlButton,
ID:=436
Application.CommandBars("Returns").Controls.Add Type:=msoControlButton,
ID:=422
End Sub

 

[Mark Ivey]

Here are a few examples you can look over that my shed some light on this
topic...

http://www.cpearson.com/excel/menus.htm

http://xl.barasch.com/ACu261595.htm

http://www.ozgrid.com/VBA/custom-menus.htm


Mark Ivey

 

[Jim Cone]

With .Controls.Add(Type:=msoControlButton)
.Style = msoButtonIconAndCaption
.Caption = "Calculate"
.OnAction = "Calculate"
.FaceId = 123
.TooltipText = "Calculates returns based on current data"
End With
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Craig Brandt"
wrote in message
I am trying to create a floating toolbar on the fly with dropdown menus and
also first level buttons. I cannot figure out how to assign an icon to a
button. Here is a piece of the code where I would like to assign an icon to
the Calculate button. How do I assign the ICON picture?
Help would be appreciated.
Thanks
Craig

Public Sub FltgToolbar()
' Establishes a Floating Toolbar for this workbook only.

Dim RvwBar As Office.CommandBar

' Close previous Toolbar
On Error Resume Next
Application.CommandBars("Returns").Delete
With Application.CommandBars.Add
.Name = "Returns"
.Left = 300
.Top = 200
.Visible = True
.Position = msoBarFloating
' Download Drop Downs
With .Controls.Add(Type:=msoControlPopup)
.Caption = "Downloads"
.TooltipText = "Imports Data into this WorkBook"

With .Controls.Add(Type:=msoControlButton)
.OnAction = "DLToday"
.Caption = "Import Current"
End With

With .Controls.Add(Type:=msoControlButton)
.OnAction = "DLPrev"
.Caption = "Import Previous"
End With
End With

' Calc Button

With .Controls.Add(Type:=msoControlButton)
.Caption = "Calculate"
.OnAction = "Calculate"
.TooltipText = "Calculates returns based on current data"
End With
End With
Application.CommandBars("Returns").Controls.Add Type:=msoControlButton,
ID:=109
Application.CommandBars("Returns").Controls.Add Type:=msoControlButton,
ID:=4
Application.CommandBars("Returns").Controls.Add Type:=msoControlButton,
ID:=1849
Application.CommandBars("Returns").Controls.Add Type:=msoControlButton,
ID:=436
Application.CommandBars("Returns").Controls.Add Type:=msoControlButton,
ID:=422
End Sub

 

[Craig Brandt]

Thanks,

Jim - I used your solution and Mark I saved you pointers.

Thanks again,
Craig

 

[Klemen25]

Hy
Just if you need, or if someone else would later: to search for ID
numbers of excel icons this is a great help:
http://skp.mvps.org/faceid.htm

Best regards