Ribbon - toggle button problems

[alex878]

Hi guys,

I’m not sure if this can be done, but I am trying to create two toggle buttons in a custom ribbon tab and make it such that:

1. When toggle button 1 is pressed, toggle button 2 is switched off
2. When toggle button 2 is pressed toggle button 1 button is switched off and so forth

My code is below and im using invalidatecontrol method to reset a button to the off state when the other is pressed.

Is there a simple way of getting this to work?

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="false">
<tabs>
<tab id="customTab" label="Custom Tab">
<group id="customGroup" label="Custom Group">
<toggleButton id="customButton1" label="Face 1" imageMso="HappyFace" size="large" onAction="Callback1" />
<toggleButton id="customButton2" label="Face 2" imageMso="HappyFace" size="large" onAction="Callback2" />
</group>
</tab>
</tabs>
</ribbon>
</customUI> 

Dim PressedState As Boolean
Public gobjRibbon As IRibbonUI

Public Sub OnRibbonLoad(objRibbon As IRibbonUI)
Set gobjRibbon = objRibbon
End Sub
Sub callback1(control As IRibbonControl, pressed As Boolean)
MsgBox "Face 1 on - Face 2 off"
gobjRibbon.InvalidateControl ("customButton2")
End Sub

Sub callback2(control As IRibbonControl, pressed As Boolean)
MsgBox "Face 2 on - Face 1 off"
gobjRibbon.InvalidateControl ("customButton1")
End Sub

Is there a simple way of getting this to work?

Thanks.

[Paul_Hossler]

You just need to use the getPressed callback and to include onLoad in your xmlns

Option Explicit
Public gobjRibbon As IRibbonUI
Public B1 As Boolean, B2 As Boolean
 
Public Sub OnRibbonLoad(objRibbon As IRibbonUI)
Set gobjRibbon = objRibbon
B1 = False
B2 = False
End Sub
 
'Callback for customButton1 onAction
Sub Pushed(control As IRibbonControl, pressed As Boolean)
Select Case control.ID
    Case "customButton1"
        B1 = True
        B2 = False
    Case "customButton2"
        B1 = False
        B2 = True
End Select
gobjRibbon.Invalidate
End Sub

'Callback for customButton1 getPressed
Sub UpOrDown(control As IRibbonControl, ByRef returnedVal)
Select Case control.ID
    Case "customButton1"
        returnedVal = B1
    Case "customButton2"
        returnedVal = B2
End Select
End Sub
 
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"onLoad="OnRibbonLoad" >
<ribbon startFromScratch="false">
<tabs>
<tab id="customTab"label="Custom Tab">
<group id="customGroup"label="Custom Group">
<toggleButton id="customButton1"label="Face 1"imageMso="HappyFace"size="large"onAction="Pushed"getPressed="UpOrDown" />
<toggleButton id="customButton2"label="Face 2"imageMso="HappyFace"size="large"onAction="Pushed"getPressed="UpOrDown" />
</group>
</tab>
</tabs>
</ribbon>
</customUI> 

Paul

[alex878]

Works really well, thanks Paul for your help.

[misi]

I had this working in Excel 2003 using commandbar coding but could not figure out how to do it with a ribbon (until I came across your solution).

Many thanks again

[Bob Phillips]

I am trying to create two toggle buttons in a custom ribbon tab and make it such that:

1. When toggle button 1 is pressed, toggle button 2 is switched off
2. When toggle button 2 is pressed toggle button 1 button is switched off and so forth

Isn't a toggle button supposed to have two states itself, that is using one to control another is a tad redundant?

[TrippyTom]

XLD: I think an example of where you might want to do this is when you need 2 distinct choices, but they're not necessarily on/off. Like "inches" vs. "centimeters"?

Or would that still be a good option for a toggle button?

[Paul_Hossler]

If it were "Inches" XOR "Centimeters" and only that ,then I'd agree with XLD about the single toggle button, probably changing the getLable results. That was the OP's origional question


If the logic were more complicated, I colud see multiple toggle buttons with a really ugly truth table


For ex TB1, TB2, TB3 (main options), TB4, TB5 (sub-options)

If TB1 = ON, then force TB4 = OFF, TB5=ON, and TB2 and TB3=OFF
If TB1 = OFF, then fore TB4 = OFF, TB5=OFF, and TB2 and TB3=OFF

If TB2 = ON, then forceTB4 = ON, TB5=OFF, and TB1 and TB3=OFF
If TB2 = OFF, then force TB4 = OFF, TB5=OFF, and TB1 and TB3=OFF

If TB3 = ON, then force TB4 = OFF, TB5=OFF, and TB1 and TB2=OFF
If TB3 = OFF, then forceTB4 = OFF, TB5=OFF, and TB1 and TB2=OFF

TB4 and TB5 still operate independently to control sub-options


So you could have TB1=ON, and then click and have TB4=ON also


Paul

[clark22]

You need to use the getPressed callback and return True or False accordingly.

[fotodj]

getPressed callback and return True or False solve the issue.

[nischalshety]

Hi Paul,
This may be outdated but just came across your solution for toggle buttons in ribbon. while it works for 2 buttons i would like it to work for 3 buttons i.e. one of the 3 button should be in pressed state. I am not a pro at vba but i tried to copy the code as in the attached file but does not work. Can you help me make it work please. I am using excel 2007.
basically i want one toggle button to choose either one of the three currencies i.e. USD, EURO, GBP
I have to use toggle button as i believe we cannot use option (radio) button in excel 2007 ribbon, which i would have loved to use instead.

Thanks in advance

[Bob Phillips]

Hi Paul,
This may be outdated but just came across your solution for toggle buttons in ribbon. while it works for 2 buttons i would like it to work for 3 buttons i.e. one of the 3 button should be in pressed state. I am not a pro at vba but i tried to copy the code as in the attached file but does not work. Can you help me make it work please. I am using excel 2007.
basically i want one toggle button to choose either one of the three currencies i.e. USD, EURO, GBP
I have to use toggle button as i believe we cannot use option (radio) button in excel 2007 ribbon, which i would have loved to use instead.

I have a query that uses those same 3 currency options; I use a dropdown.

[Paul_Hossler]

Hi Paul,
This may be outdated but just came across your solution for toggle buttons in ribbon. while it works for 2 buttons i would like it to work for 3 buttons i.e. one of the 3 button should be in pressed state. I am not a pro at vba but i tried to copy the code as in the attached file but does not work. Can you help me make it work please. I am using excel 2007.
basically i want one toggle button to choose either one of the three currencies i.e. USD, EURO, GBP
I have to use toggle button as i believe we cannot use option (radio) button in excel 2007 ribbon, which i would have loved to use instead.

Thanks in advance

One way - you can see the XML and the Callback in the xlsm

Capture.JPG

[nischalshety]

Thanks xld for your post, yes drop down can also be done, but i will be using the three toggle buttons also for other codes apart from currencies, like measurements or maybe to get data for different locations. here the dropdown can work but large toggle buttons if feel can be more useful for my purpose.

Apreciate your feed back though...

[nischalshety]

Paul, thats what i wanted, as i said i am really new to coding in VBA or XMLs i will surely study your code and try to make necessary changes to suit my requirement.
Appreciate such a quick response buddy
Stay healthy and merry xmas to you

[MarcoAmaral]

Please i would like to put a color on the ToggleButton of a ribbon when pressed.

[Rolly_Sefu]

Hello.

Thanks a lot for the reply's on this thread they where, very useful, especially the excel files.

I also create a version for my needs.

i made the code protect and unprotect the active sheet based on the button click.

Option Explicit
Public gobjRibbon As IRibbonUI
Public B1 As Boolean, B2 As Boolean
 
Public Sub OnRibbonLoad(objRibbon As IRibbonUI)
Set gobjRibbon = objRibbon
If ActiveSheet.ProtectContents = True Then
    B1 = True
    B2 = False
Else
    B1 = False
    B2 = True
End If
End Sub
'Callback for customButton1 onAction
Sub Pushed(control As IRibbonControl, pressed As Boolean)
Dim user, x As String
user = Environ("Username")
On Error Resume Next
x = Application.WorksheetFunction.VLookup(user, Sheets("Stuff").Range("Login"), 1, False)
Select Case control.ID
    Case "Togglebutton1"
        If Err.Number > 0 Then
            MsgBox "I'm sorry, you do not have access to LOCK this file !", vbCritical
            B1 = True
            B2 = False
        Else
            On Error GoTo 0
            ActiveSheet.Protect Password:=pwd, DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True
            B1 = True
            B2 = False
        End If
    Case "Togglebutton2"
        If Err.Number > 0 Then
            MsgBox "I'm sorry, you do not have access to UNLOCK this file !", vbCritical
            B1 = True
            B2 = False
        Else
            On Error GoTo 0
            ActiveSheet.Unprotect Password:=pwd
            B1 = False
            B2 = True
        End If
End Select
gobjRibbon.Invalidate
End Sub
'Callback for customButton1 getPressed
Sub UpOrDown(control As IRibbonControl, ByRef returnedVal)
Select Case control.ID
    Case "Togglebutton1"
        returnedVal = B1
    Case "Togglebutton2"
        returnedVal = B2
End Select
End Sub

The problem is that the workbook has multiple sheets and i wanted to buttons to change based on the selection of the sheet, if it is protected or not.
VBA i placed into the worksheets:

Private Sub Worksheet_Activate()
Dim FakeControl As IRibbonControl
Module3.OnRibbonLoad FakeControl
Module3.UpOrDown FakeControl
End Sub

The code runs OnRibbonLoad, but i get an error for UpOrDown
Help please.

[Paul_Hossler]

1. Welcome to VBAexpress - please take a minute and read the links in my signature

2. Do-able. I did something similar in post #13 in

http://www.vbaexpress.com/forum/showthread.php?64638-RibbonX-Setting-pressed-state-of-Checkbox-control-at-runtime

so take a look are the attachment and the worksheet events. If you have question, then attach a small sample workbook with the CustomUI and macros

3. Since this is / was a very old thread (started in 2012) you can get better visibility by starting your own


It seems like you want to add two TBs, one says "Protect" and the other "Unprotect" with the .ProtectContents of the Active determining which is pressed and which is not???

I'd suggest just one Button and use getLable and getImage

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    oRibbon.Invalidate
End Sub

Option Explicit

Public oRibbon As IRibbonUI

'Callback for customUI.onLoad
Sub OnRibbonLoad(ribbon As IRibbonUI)
    Set oRibbon = ribbon
End Sub


'Callback for bProtect getLabel
Sub GetLable(control As IRibbonControl, ByRef returnedVal)
    returnedVal = IIf(ActiveSheet.ProtectContents, "Unprotect", "Protect")
End Sub


'Callback for bProtect onAction
Sub OnAction(control As IRibbonControl)
    If ActiveSheet.ProtectContents Then
        ActiveSheet.Unprotect
    Else
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If
    
    oRibbon.InvalidateControl ("bProtect")
End Sub

'Callback for bProtect getImage
Sub GetImage(control As IRibbonControl, ByRef returnedVal)
    returnedVal = IIf(ActiveSheet.ProtectContents, "Delete", "MarkTaskComplete")
End Sub

[Rolly_Sefu]

Hello, thanks for the reply.

Your file works just fine, i just wanted to have 2 buttons ( but one button works as well )

I looked that the link and post 13, but could not figure out how to fix my problem
Unfortunately i could not fix my code to work, i attached a file.

So i wanted to modify your code and add my pictures, i use a file "RibbonEditor.xlam" to add the custom ribbon, and i could not find any buttons that you added in your workbook.

Are you using any other program ?

I can't use a program since work group policy does not allow to install programs, that's why i use the file.



Could you maybe take a look at my file ? Maybe you know how to fix it.

The system works: Lock and Unlock the sheets by pressing one button the other one deactivates, and i get the correct state for the sheet on workbook open

I just want the button state to change if i change to a protected or unprotect sheet.

Any Ideas ?

[Paul_Hossler]

I changed some variable names so that I could keep them straight, but the biggest change was in SheetActivate

Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
        
    If Sh.ProtectContents Then
        bUnlocked = False
        bLocked = Not bLocked
    Else
        bLocked = False
        bUnlocked = Not bLocked
    End If
    
    gobjRibbon.Invalidate
End Sub

[Rolly_Sefu]

Hy, it's exactly what i wanted, thank you.
Now on workbook open it does not select the state of the button until you change the sheet, do you know why ?
And a strange this is that after you protect / unprotect the sheet manually when you change the sheet another sheet and then back the state remains false for the 2 buttons. do you know why ?