PDA

View Full Version : [SOLVED] Ribbon - toggle button problems



alex878
12-02-2012, 09:01 PM
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
12-03-2012, 01:08 PM
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
12-03-2012, 04:16 PM
Works really well, thanks Paul for your help.

misi
12-28-2012, 03:27 PM
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
01-01-2013, 06:15 PM
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
03-30-2013, 02:39 AM
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
03-30-2013, 05:55 AM
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
06-17-2013, 09:14 PM
You need to use the getPressed callback and return True or False accordingly.

fotodj
07-13-2014, 06:43 PM
getPressed callback and return True or False solve the issue.

nischalshety
12-23-2015, 10:32 AM
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
12-23-2015, 11:19 AM
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
12-23-2015, 01:36 PM
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

15031

nischalshety
12-23-2015, 10:10 PM
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
12-23-2015, 10:13 PM
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
02-07-2019, 09:37 AM
Please i would like to put a color on the ToggleButton of a ribbon when pressed.

Rolly_Sefu
02-27-2019, 03:48 AM
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
02-27-2019, 09:29 AM
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
02-28-2019, 12:23 AM
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
02-28-2019, 08:33 AM
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
03-01-2019, 02:02 AM
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 ?

Rolly_Sefu
03-01-2019, 02:57 AM
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 )

Paul_Hossler
03-01-2019, 09:38 AM
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

Rolly_Sefu
03-04-2019, 01:29 AM
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 :)

Paul_Hossler
03-04-2019, 05:59 AM
Try this version

I added a Workbook_Open and cleanup up some other code

I "think' it fixes those two issues

Rolly_Sefu
03-04-2019, 11:36 PM
Hello, works perfectly, thank you.

Paul_Hossler
03-05-2019, 08:19 AM
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

Rolly_Sefu
07-08-2019, 01:41 AM
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.


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.

Paul_Hossler
07-12-2019, 09:06 AM
It's a whole lot easier if you can post small sample workbook with enougth sheets to show problem

Rolly_Sefu
07-12-2019, 01:06 PM
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.

Paul_Hossler
07-12-2019, 04:51 PM
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




'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

Rolly_Sefu
07-14-2019, 10:49 PM
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.

Paul_Hossler
07-19-2019, 08:09 AM
Here's a more cleaned version if you want it

I added button labels to make it a little more explainitory

Rolly_Sefu
07-22-2019, 10:58 PM
It's great, thank you :)