Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 33

Thread: Ribbon - toggle button problems

  1. #1
    VBAX Regular
    Joined
    May 2012
    Posts
    15
    Location

    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?

    <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.
    Last edited by Aussiebear; 04-25-2023 at 07:13 PM. Reason: Adjusted the code tags

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    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
    Attached Files Attached Files
    Last edited by Aussiebear; 04-25-2023 at 07:16 PM. Reason: Adjusted the code tags

  3. #3
    VBAX Regular
    Joined
    May 2012
    Posts
    15
    Location
    Works really well, thanks Paul for your help.

  4. #4
    VBAX Regular
    Joined
    May 2012
    Posts
    6
    Location

    Talking Paul - loads of kudos to you !!!!

    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

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    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?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    VBAX Expert TrippyTom's Avatar
    Joined
    Jul 2005
    Location
    New York, NY (USA)
    Posts
    556
    Location
    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?
    Office 2010, Windows 7
    goal: to learn the most efficient way

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    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

  8. #8
    VBAX Newbie
    Joined
    Jun 2013
    Posts
    1
    Location
    You need to use the getPressed callback and return True or False accordingly.

  9. #9
    VBAX Regular
    Joined
    Jul 2014
    Posts
    14
    Location
    getPressed callback and return True or False solve the issue.

  10. #10
    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
    Attached Files Attached Files

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    Quote Originally Posted by nischalshety View Post
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #12
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    Quote Originally Posted by nischalshety View Post
    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

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

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

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

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

  17. #17
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    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


    Attached Files Attached Files
    Last edited by Paul_Hossler; 02-27-2019 at 09:59 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  18. #18

    Change Toggle button state on sheet change

    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 ?
    Attached Files Attached Files

  19. #19
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    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
    
    
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  20. #20
    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 ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •