How-to: Toggle the button face on a custom toolbar?

[Air_Cooled_Nut]

I have a custom toolbar that works fine. One of the buttons is a toggle: Click it and columns are hidden, click it again and the columns are unhidden -- very simple and it works. I would like to change the button face in response. So when the toolbar is first created it has a default button face. Clicking the button will run the macro and change the button face. Click the button again and the macro runs again and changes the button face back.

TIA!

[davesexcel]

I have a custom toolbar that works fine. One of the buttons is a toggle: Click it and columns are hidden, click it again and the columns are unhidden -- very simple and it works. I would like to change the button face in response. So when the toolbar is first created it has a default button face. Clicking the button will run the macro and change the button face. Click the button again and the macro runs again and changes the button face back.

TIA!

I am not sure if this will work in your situation, but take a look anyway, you might get an idea:

Private Sub ToggleButton1_Click()
If ToggleButton1 = True Then
ToggleButton1.Caption = "Hidden"
MsgBox "Hidden" 'your code here
Else: ToggleButton1.Caption = "View"
MsgBox "Column view" 'your code here
End If
End Sub

[Air_Cooled_Nut]

No, it's not the Caption that I want to change, I want the actual button face to change.

However, thanks for mentioning that! That is a great idea to change the tooltip (mouse hover?) I can put that into my code.

[dominicb]

Good evening Air_Cooled_Nut

Without seeing the code you've got so far it's hard to relate this directly to your case, but you need to be exploring the State properties of the relevent control, the property in question needs to be set to msoButtonUp or msoButtonDown, thus :

Application.CommandBars(1).Controls("My Button1").Controls(1).State = msoButtonUp
Application.CommandBars(1).Controls("MyButton1").Controls(1).State = msoButtonDown

HTH

DominicB

[Air_Cooled_Nut]

Create the toolbar:

Private Sub AddToolBar()
    Dim tBar As CommandBar, NewButton As CommandBarButton
    Set tBar = Application.CommandBars.Add
    With tBar  'First create the toolbar
        .Name = ASTRO_TOOLBAR_NAME
        .Visible = True
        .Protection = msoBarNoCustomize + msoBarNoResize
        .Position = msoBarBottom
    End With
    'Now set up the buttons
    Set NewButton = Application.CommandBars(ASTRO_TOOLBAR_NAME).Controls.Add(Type:=msoControlButton)
    With NewButton
        .FaceId = 462  '464 is the opposite button
        .OnAction = "SimpleView"
        .Caption = "Toggle Simple/Full View"
    End With
...

The subroutine used by the toolbar button and where the SPECIFIC toolbar button would be changed:

Sub SimpleView()
'Right now this just hides/shows columns
'Need to implement code to change toolbar button
'Dim tBar As CommandBar
'Set tBar = Application.CommandBars

If Columns("A:A").EntireColumn.Hidden Then
    Columns("A:A").EntireColumn.Hidden = False
    Columns("B:B").EntireColumn.Hidden = False
    Columns("I:K").EntireColumn.Hidden = False
    'Put code here to change the FaceID = 464
Else
    Columns("A:A").EntireColumn.Hidden = True
    Columns("B:B").EntireColumn.Hidden = True
    Columns("I:K").EntireColumn.Hidden = True
    'Put code here to change the FaceID = 462
End If
End Sub

Also, is there are way to give each toolbar button an identification or are they automatically assigned an index number (Item() )? Maybe this would make it easier? I don't know.

[dominicb]

Good evening Air_Cooled_Nut

Something like this seems to work OK :

Sub SimpleView()
'Right now this just hides/shows columns
'Need to implement code to change toolbar button
'Dim tBar As CommandBar
'Set tBar = Application.CommandBars

If Columns("A:A").EntireColumn.Hidden Then
    Columns("A:A").EntireColumn.Hidden = False
    Columns("B:B").EntireColumn.Hidden = False
    Columns("I:K").EntireColumn.Hidden = False
    Application.CommandBars(ASTRO_TOOLBAR_NAME).Controls(1).State = msoButtonDown
    'Put code here to change the FaceID = 464
Else
    Columns("A:A").EntireColumn.Hidden = True
    Columns("B:B").EntireColumn.Hidden = True
    Columns("I:K").EntireColumn.Hidden = True
    Application.CommandBars(ASTRO_TOOLBAR_NAME).Controls(1).State = msoButtonUp
    'Put code here to change the FaceID = 462
End If
End Sub

HTH

DominicB