How to toggle the state of a toolbar button? 1

[N1GHTEYES]

There are various standard toolbar button whose state toggles - e.g. format painter, select objects etc... I would like to have a button which does the same thing.

Is this behaviour a property of the button somehow? In which case, how does one choose a "toggleable" button as opposed to a "normal" one? If not, I guess I could have the activated procedure itself cause the state of the button to change, but I'm not sure about the best way to do that.

Can anybody offer any advice please?

Tony

 

[N1GHTEYES]

I've found a solution using a text-style for the button. It works and it does the job I need it to do, but it would still be nicer to have an image version. So if anyone has any clues as to how, I'm still open to suggestions.

Clicking the button triggers the procedure SetText. Here is the code:

Public Sub SetText()
With CommandBars("QASI").Controls((Application.Caller(1)))
    If .Caption = "Inc Text" Then
        UseUserForm = True
        .Caption = "No Text"
    Else
        UseUserForm = False
        .Caption = "Inc Text"
    End If
End With
End Sub

The idea is that it toggles both the appearance of the button and the state of the global variable "UseUserform". The button is on a toolbar called "QASI".

Tony

 

[SkipVought]

I'm not sure I understand where you are going with this.

I did this little experiment.
[tt]
Select a range of cells

Right-Click > Format Cells > Alignment TAB >

and then change the alignment to something OTHER THAN what you see in the Ribbon.

Click OK
[/tt]
...and you immediately see the format settings reflected in the icon controls in the Ribbon, WITHOUT trying to TOGGLE ANYTHING!!!

?????

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Notice how the icon states are CONTEXT SENSITIVE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

And, it seems, only SOME of the icon controls fall into this category of being context sensitive. Other controls are not.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Ribbon? Cells? Huh?

Maybe this is a version confusion. I'm (currently) using 2003.

I have created a toolbar. It has some buttons on it.
One of the buttons does not "do" anything active in the sheet as such, but it does change the state, such that when a different button is subsequently used, that button's macro behaves differently depending on which state the system is in. This behaviour is similar to what happens when you click the "select objects" button on the drawing toolbar - i.e. it changes the appearance of the cursor and the selection mode. Furthermore, it changes the appearance of the button itself. That is what I'm trying to do here.

When the workbook starts, it is in UseUserForm=True mode and the button says "No Text". If I click the button, it changes to UseUserForm=False and the button changes to "Inc Text".

Tony

 

[combo]

You can do it with code generated buttons. Assuming you have a "Custom_1 commandbar:

Sub SetCommandBar()
Dim b As CommandBar
Dim c As CommandBarButton
Set b = Application.CommandBars("Custom_1")
For i = 1 To b.Controls.Count
    b.Controls(1).Delete
Next i
With b
    Set c = b.Controls.Add(Type:=msoControlButton)
    With c
        .FaceId = 2
        .TooltipText = "state ""up"""
    End With
    Set c = b.Controls.Add(Type:=msoControlButton)
    With c
        .FaceId = 5
        .OnAction = "ChangeButton"
        .TooltipText = "chanage first button's state"
    End With

End With
End Sub

Sub ChangeButton()
With Application.CommandBars("Custom_1").Controls(1)
    If .State = msoButtonDown Then
        .State = msoButtonUp
        .TooltipText = "state ""up"""
    Else
        .State = msoButtonDown
        .TooltipText = "state ""down"""
    End If
End With
End Sub

combo

 

[N1GHTEYES]

combo, thanks for the response. I think that is something like the approach I need, but it is not right yet.

In SetCommandBar, you delete the existing buttons on the toolbar - I do not want to do this and I don't understand why I would need to.

In the second, you use the .state property. I tried that, but found that the state is always "up" when the button is pressed, regardless of what .state I set in the code.

Perhaps I'd best expand briefly:
I've written a workbook-based tool. It allows the user to create a particular type of diagram. It has a toolbar with 14 buttons. Several of these allow the user to add shapes to the diagram. The user will want to enter text into the shapes he adds. He can do this manually, in the shape itself, but when the diagram is zoomed out to see the whole, the text is too small to read. So, as part of the function of the shape creation buttons, I've included a short procedure where a userform is presented on which there is a text box the user can use to enter the text.

However, if he is only working in a small section of the diagram this is not necessary and requires more activity for the user than if he entered the text directly. So, what I need is a global boolean which the shape-drawing routines can see to determine whether to include the useform section or not.

That is where this button comes in. I have added the required button to the toolbar, created an appropriate face for it and pointed it at the appropriate sub. What I want the sub to do is:

toggle the UseUserForm boolean
toggle the appearance of the button (i.e. latch it in "depressed" state when it is clicked then "release" it when it is clicked again - pretty much like the standard buttons "format painter" button, or "select objects".

I did try your changebutton code method, but, as I say neither the appearance of the button nor the .state property seem to change.

The code I presented in my second post does what I need, but only if I use a text button. I'd rather use an icon.

I could of course use .faceid in the same way I use the text (to display the state to the user and to determine the current state) but I don't know how to define a faceID for my custom image. What I want to be able to do is capture the image of the depressed button and make it stay that way rather than releasing after clicking. I thought .state would do that, but it does not seem to do so.

Here is the code I used to try the .state manipulation:

Sub SetText()
With Commandbars("QASI").controls(Application.Caller(1)))
    If UseUserForm then 
         UseUserForm = False
         .state = msoButtonUp
         .TooltipText = "Inc Text"
    Else
         UseUserForm = True
         .state = msoButtonDown
         .TooltipText = "No Text"
    End If
End With
End Sub

This works, in the sense that the state and the tooltip change, but the appearance of the button does not.

Tony

 

[combo]

There is no need to delete buttons. However, to work, they have to be created by code:

However the State property of built-in command bar buttons is read-only

This can be a reason that the macro buttons don't accept State settings.

combo

 

[N1GHTEYES]

Ah, I missed that. Now I understand. Thanks for that. I hate it when it doesn't work and I don't understand why! Have a star for enlightening me.

So if I want the .state functionality, I have to have created the button myself in code.

I could include that later, but it seems like it is starting to introduce potential problems for maintenance if I change anything in the future.

For now, what I have done is create two different images - one showing an icon of a text entry screen and the other showing the same screen with a cross through it. These are named "IncText" and "NoText" respectively. I put them on the Instructions page (where they are needed anyway) to explain the functions of the toolbar to the user. The code behind the button is now:

Sub SetText()
With CommandBars("QASI").Controls((Application.Caller(1)))
    If UseUserForm = False Then
        UseUserForm = True
        Worksheets("Instructions").Shapes("IncText").Copy
        .PasteFace
        .TooltipText = "No Text"
    Else
        UseUserForm = False
        Worksheets("Instructions").Shapes("NoText").Copy
        .PasteFace
        .TooltipText = "Inc Text"
    End If
End With
End Sub

This causes the required state change and also reflects the current state in the appearance of the button. Not exactly how I was originally trying to do it, but close enough for now.

Tony

 

[combo]

Thanks for *.
There are hundreds of built-in FaceIDs no problem to quickly test them by code and maybe apply some.
Commandbars are sticky, there's a risk that they can remain without underlying code. For the purpose of distribution and cleaning-up I prefer to create and destroy them either with Install/Uninstall for add-ins or open/close for both add-in and workbook.

combo

 

[N1GHTEYES]

I've attached the toolbar to the workbook, and have this code in the WorkBook_BeforeClose event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("QASI").Delete
Err.Clear
End Sub

That seems to stop it from sticking to excel. Certainly, it disappears from the toolbars area when I close the workbook.

Tony