Excel Tool Bar. (Is this possible "2") 1

[jasonmac]

Hi everyone. I have another one of those "Is this possible?" questions.

I have created a custom command button and placed it on a toolbar in my Excel application. The acts as a toggle, opening and closing a connection to another piece of equipment.

I would like this button to change its image to reflect wheather the connection is open or closed. Originally this was accomplished by using a command button on the worksheet and coding for the caption property.

Do commands on a toolbar have properties like other controls? If so how do you get to them? Can anyone point me in the right direction?

Thanks in Advance,
Jason

 

[TonyJollans]

Hi Jason,

They are not the easiest things to work with but toolbar objects all have properties. You need to have a reference to the button, something like:

Dim cbr as CommandBar
Dim btn as CommandBarControl
Set cbr = Application.CommandBars("Standard")

' A built-in toolbar

Set cbr = Application.CommandBars("Custom 1")

' Your custom toolbar perhaps

Set btn = cbr.Controls("Custom Button")

      ' Or whatever yors is called

Msgbox btn.Caption

' for example

Msgbox btn.FaceId

 ' the property you might want

There are several types of CommandBarControls, (not all of which you can create for yourself) and different types have different properties so you need to be careful (particularly in a For Each loop). If you have a button, it should have a FaceId - understanding them, of course, is another matter, but there are utilities around (search the forums here and you should find some references) which might help with choosing them.

Enjoy,
Tony

 

[VBAjedi]

If you create the button via code, you can access these properties when you create it. Here's a bit of code I use to create a custom toolbar when a workbook is opened. This particular one is floating, but you could make yours docked instead. I have simplified it here to only have one button on it, even thought I usually have several. It would be easy to modify this to add a button to an existing toolbar instead. In either case, you can set the FaceID when you create the button, and get the buttons Index value at that time so you can reference it later to chance the FaceID. Here's the code:

Sub CreateToolbars()
Dim CustBar As CommandBar
Dim CustBar1 As CommandBar
Dim CustButton1, CustButton2

'-- Delete existing custom toolbar of same name if it exists
For Each CustBar In Application.CommandBars
    If CustBar.Name = "My Custom Tools" Then
        CustBar.Delete
    End If
Next
 
Set CustBar1 = Application.CommandBars.Add("My Custom Tools")
    
'--Add buttons.
With Application
    Set CustButton1 = CustBar1.Controls _
        .Add(Type:=msoControlButton)
    With CustButton1
        .FaceId = 596
        .Caption = "&Reset Filters"
        .OnAction = "ShowAllData"
        .Style = 3 ' msoButtonIconAndCaption
    End With
    
    Set CustButton2 = CustBar1.Controls _
        .Add(Type:=msoControlButton)
    With CustButton2
        .FaceId = 44
        .Caption = "&Make Exec Rpt"
        .OnAction = "ExecReport"
        .Style = 3 ' msoButtonIconAndCaption
    End With
    
    CustBar1.Visible = True
    CustBar1.Position = msoBarFloating
End With

End Sub

The advantage of creating your toolbar at runtime is that it comes back even if someone accidentally deletes it.

If you're interested, I could email you a workbook I have (can't remember where I got it) that will show you all the button icons and their related ID number.

VBAjedi

 

[jasonmac]

Thanks to both of you. That's exactly what I was looking for!