Ribbon - toggle button problems

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?

Code:

---

<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.

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

Code:

---

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

Works really well, thanks Paul for your help.

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

Quote:

---

Originally Posted by alex878

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?

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?

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

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

getPressed callback and return True or False solve the issue.

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

Quote:

---

Originally Posted by 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.

---

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

Quote:

---

Originally Posted by 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

---

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

Attachment 15031

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...:)

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 :)

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

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.

Code:

---

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:

Code:

---

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.

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

Code:

---


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


---

Code:

---

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

---

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 ?

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

Code:

---

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



---

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 ?

Try this in the workbook that you sent me:
1. open workbook
2. go to sheet 1, press lock
3. go to sheet 2, press lock
4. go to sheet 3, press lock
5. just switch between the sheets, go from sheet 1 to sheet 2 to sheet 3.
The buttons state does not update :( ( one or more sheets have the two buttons at state false )

try this

i cleand up the logic and code a little more

if you make a manual protection change, i don't know of any way to update the ribbon, but if you switch out and back in it should update

Hello
Thanks a lot, now it works perfectly.
I commented this line: Err.Clear '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Because the Err.Clear removed the error number witch i used to allow specific people to lock or unlock the sheets.
And with the line active the msgbox's did not appear if user was not found in the first sheet.
But now it works perfectly, thank you very much :)

Try this version

I added a Workbook_Open and cleanup up some other code

I "think' it fixes those two issues

Hello, works perfectly, thank you.

Quote:

---

Originally Posted by Rolly_Sefu

Hello
I commented this line: Err.Clear '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

---

Yes, I used that to bypass the password checking since I was only interested in getting the buttons to work the way you wanted

Hy, this is regarding to the same thread, same file.

I need to modify the code a little bit:
- when i click on the lock button, it will password protect all cells in the sheet.
- when i click on the unlock button, i want to unprotect the sheet, unlock only column H, then protect the sheet and switch the toggle button to unlocked

I modified the code for the column H part, but i just can't get the toggle button to stay on unlocked.
The code below resets the toggle buttons
If i remove the L1="Yes" from the end when i click on unprotect it will just turn on the lock toggle button.

Code:

---

Option Explicit

Public gobjRibbon As IRibbonUI
 
Public Sub OnRibbonLoad(ribbon As IRibbonUI)
Set gobjRibbon = ribbon
gobjRibbon.Invalidate
End Sub


'Callback for customButton1 onAction
Public Sub Pushed(Control As IRibbonControl, pressed As Boolean)
Dim user As String, X As String, Y 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 "tbLocked"
        If Err.Number > 0 Then
            MsgBox "I'm sorry, you do NOT have access to LOCK this sheet !", vbCritical
        Else
            On Error GoTo 0
            If ActiveSheet.Name <> "WRS AR employees" Then
                ActiveSheet.Protect Password:=pwd, UserInterfaceOnly:=True, DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True
                Columns("H:H").EntireColumn.Locked = True
                ActiveSheet.Range("L1") = "No"
            Else
                MsgBox "I'm sorry, you do NOT have access to LOCK this sheet !", vbCritical
            End If
        End If
    Case "tbUnlocked"
        If Err.Number > 0 Then
            MsgBox "I'm sorry, you do NOT have access to UNLOCK this sheet !", vbCritical
        Else
            On Error GoTo 0
            If ActiveSheet.Name <> "WRS AR employees" Then
                ActiveSheet.Unprotect Password:=pwd
                On Error Resume Next
                Y = Application.WorksheetFunction.VLookup(user, Sheets("Stuff").Range("Access"), 1, False)
                If Err.Number = 0 Then
                    Columns("H:H").EntireColumn.Locked = False
                    ActiveSheet.Protect Password:=pwd, UserInterfaceOnly:=True, DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True
                    ActiveSheet.Range("L1") = "Yes"
                End If
                On Error GoTo 0
            Else
                MsgBox "I'm sorry, you do NOT have access to UNLOCK this sheet !", vbCritical
            End If
        End If
End Select
gobjRibbon.Invalidate
End Sub


'Callback for customButton1 getPressed
Public Sub UpOrDown(Control As IRibbonControl, ByRef returnedVal)
If ActiveSheet.Range("L1") = "Yes" Then
    returnedVal = Not ActiveSheet.ProtectContents
    Exit Sub
End If
Select Case Control.ID
    Case "tbLocked"
        returnedVal = ActiveSheet.ProtectContents
    Case "tbUnlocked"
        returnedVal = Not ActiveSheet.ProtectContents
End Select
End Sub

---

Any ideas ?
Thank you.

It's a whole lot easier if you can post small sample workbook with enougth sheets to show problem

Hello

I have attached a file.

So the modification to the previous version is that:

1. on lock => all cells are locked ( lock toggle button ON )
2. on Unlock => all cells are locked, EXCEPT cells in column H ( unlock toggle button ON )

The problem is that with the modification i have made when i click on unlock, the 2 toggle buttons deselect :(

Any ideas ?
Thanks.

I didn't trace through the whole thing, but I think that the sheet lunie is always protected

[OVT Locked] protects the entire sheet, including col H
[OVT Unlocked] protects the entire sheet, except col H

So "

ActiveSheet.ProtectContents" is always true

It might be better to just test if Col H is locked instead: "ActiveSheet.range("H:H").locked"


I didn't understand the L1 part

Code:

---

'Callback for customButton1 getPressed
Public Sub UpOrDown(Control As IRibbonControl, ByRef returnedVal)

'If ActiveSheet.Range("L1") = "Yes" Then
'    returnedVal = Not ActiveSheet.ProtectContents
'  Exit Sub
'End If

Select Case Control.ID
    Case "tbLocked"
        returnedVal = ActiveSheet.Range("H:H").Locked
    Case "tbUnlocked"
        returnedVal = Not ActiveSheet.Range("H:H").Locked
End Select
End Sub




---

Hello,

The L1 part was just a noob thinking ... did not think to check directly if column H is locked or not ( i removed all L1 lines )

I tryed the lines "returnedVal = ActiveSheet.Range("H:H").Locked" and they work perfectly.
Exactly what i needed.

Many thanks.

Here's a more cleaned version if you want it

I added button labels to make it a little more explainitory

It's great, thank you :)