Results 1 to 5 of 5

Thread: Simulate click on user defined ribbon button

  1. #1
    Acolyte misi01's Avatar
    Join Date
    Jul 2011
    Location
    Stockholm,Sweden
    Posts
    31
    Articles
    0

    Simulate click on user defined ribbon button



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have experimented with this back and forth and can't figure out what to do (and I'm guessing the answer is trivial. Ho-hum).

    Here's my callback

    Code:
    'Callback for Toggle_subtotals onAction
    Private Sub Toggle_subtotals_click(control As IRibbonControl)
       
        Select Case bButtonClicked
        
            Case True
                ' They've inserted subtotals - remove then
                bButtonClicked = False
                Range("Subtotals_exist").Value = 0 ' Update the technical sheet with whether subtotals exist on sheet 1 (they don't)
                Application.ScreenUpdating = False
                Call Module1.remove_subtotals("")
                Application.ScreenUpdating = True
            Case False
                ' Insert subtotals
                bButtonClicked = True
                Range("Subtotals_exist").Value = 1 ' Update the technical sheet with whether subtotals exist on sheet 1 (they do)
                Application.ScreenUpdating = False
                Call Module1.create_outline_groups("")
                 Application.ScreenUpdating = True
        End Select
        
        ' Invalidate the control so that the label/icon are updated
        rxIRibbonUI.InvalidateControl (control.ID)
        
    End Sub
    What I want to be able to do is to call this procedure from another one. For example, assume the user selects a user defined button from the ribbon that updates sheet 1. At the end of this procedure, I want to be able to update the Toggle_subtotals button. This is defined as

    Code:
    <button id="Toggle_subtotals" 
                                    keytip="T3"
                                    getImage="rxbtnProcess_getImage"
                                    getLabel="rxlblFeedback_getLabel" 
                                    onAction="Toggle_subtotals_click"/>
    If I manually click on the button, everything works fine. My problem is that I "can't" call this procedure without passing the control argument and I don't know how to do that.
    Here is the code I'm using (which doesn't work)
    Code:
    Sub test_ribbon()
        Call Ribbon_modules.refresh_ribbon("")
    End Sub
    and

    Code:
    Sub refresh_ribbon(dummy As String)
    
       ' Invalidate the ribbon so that the label/icon is updated
      '  rxIRibbonUI.Invalidate
      bButtonClicked = Range("Subtotals_exist").Value
      Call Toggle_subtotals_click("Toggle_subtotals")
      
      ' rxIRibbonUI.InvalidateControl ("Toggle_subtotals")
    
    End Sub
    The various commented lines indicate variations I've tried (that didn't work either)

    Suggestions gratefully received.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Okay, so you are capturing the Ribbon to the rxIRibbonUI object in an onLoad statement, yes?

    Just looking at your code, I'm thinking that it should work as follows:

    Code:
    Sub Refresh_Subtotal_Button()
    
      bButtonClicked = Range("Subtotals_exist").Value
      rxIRibbonUI.InvalidateControl ("Toggle_subtotals")
    
    End Sub
    Personally, what I do with this kind of thing is to write the "ButtonClicked" to somewhere for storage. A worksheet range would work, which it looks like you're using, but I often use a CustomDocumentProperty for this purpose. This means that I can set/update the value whenever I need to, then invalidate the control immediately afterwards to make it take effect.

    If this doesn't work, posting a sample workbook would make debugging this one MUCH easier. Remove anything sensitive from the workbook though. (Hopefully you can without breaking stuff.)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Acolyte misi01's Avatar
    Join Date
    Jul 2011
    Location
    Stockholm,Sweden
    Posts
    31
    Articles
    0

    Thanks Ken.

    Seems like I've been wasting your time. Even though I'd included the code

    Code:
    ' rxIRibbonUI.InvalidateControl ("Toggle_subtotals")
    (albeit commented since I didn't think it worked), I tested a bit more and
    you were right - it was the way to go.

    I think (?) what confused me was that I expected (when debugging) to run
    through the Toggle_subtotals_click procedure and when that didn't happen,
    automatically (?) assumed that the ribbon hadn't changed.

    Thanks again for pointing me in the right direction. (And hopefully, other
    people might be able to Google this topic and find your answer as well)

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Not a worry!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Acolyte misi01's Avatar
    Join Date
    Jul 2011
    Location
    Stockholm,Sweden
    Posts
    31
    Articles
    0

    Ken - can I please revisit this topic

    I was mucking around with some changes to my code - one of which required me to set one of the button ID's to a specific value. Here is my ribbon code
    Code:
    Option Explicit
    
    Dim bButtonClicked As Boolean
    Dim rxIRibbonUI As IRibbonUI
    'Dim subtotal_control As IRibbonControl
    'Callback for customUI.onLoad
    Private Sub rxIRibbonUI_onLoad(ribbon As IRibbonUI)
       
      Set rxIRibbonUI = ribbon
      ' Get whether we've saved the workbook with or without subtotals
      bButtonClicked = Range("Subtotals_exist").Value
        
    End Sub
    
    'Callback for rxlblFeedback getLabel
    Private Sub rxlblFeedback_getLabel(control As IRibbonControl, ByRef returnedVal)
         
      Select Case bButtonClicked
        Case True
          returnedVal = "Remove subtotals"
        Case False
          returnedVal = "Create subtotals"
      End Select
    
    End Sub
    
    'Callback for rxbtnProcess getImage
    Private Sub rxbtnProcess_getImage(control As IRibbonControl, ByRef returnedVal)
         
      Select Case bButtonClicked
        Case True
          returnedVal = "OutlineUngroup"
        Case False
          returnedVal = "OutlineGroup"
      End Select
        
    End Sub
    
    'Callback for Toggle_subtotals onAction
    Sub Toggle_subtotals_click(control As IRibbonControl)
        
      Select Case bButtonClicked
        
        Case True
          ' They've inserted subtotals - remove then
          bButtonClicked = False
          Range("Subtotals_exist").Value = 0
          Application.ScreenUpdating = False
          Call Module1.remove_subtotals("")
          Application.ScreenUpdating = True
        Case False
          ' Insert subtotals
          bButtonClicked = True
          Range("Subtotals_exist").Value = 1
          Application.ScreenUpdating = False
          Call Module1.create_outline_groups("")
          Application.ScreenUpdating = True
      End Select
        
      ' Invalidate the control so that the label/icon are updated
      rxIRibbonUI.InvalidateControl (control.ID)
        
    End Sub
    'Callback for UpdateButton onAction
    Private Sub update_data_ribbon(control As IRibbonControl)
      Call Module1.clear_sheet1("")
    End Sub
    Private Sub update_db_cr_details(control As IRibbonControl)
      Call Module1.import_db_cr_details("")
    End Sub
    Private Sub update_account_structure_details(control As IRibbonControl)
      Call Module1.import_account_structure("")
    End Sub
    Sub refresh_subtotal_ribbon(dummy As String)
      
      bButtonClicked = Range("Subtotals_exist").Value
      ' Invalidating the control will automatically result in it being updated
      rxIRibbonUI.InvalidateControl ("Toggle_subtotals")
    
    End Sub
    The actuL VBA code I'm running is as follows:-
    Code:
    Sub import_account_structure(dummy As String)
    
      Dim sw_error As Boolean
      
      If Range("Subtotals_exist").Value = 1 Then
        ' They've created subtotals - remove them.
        ' We MUST set the named range for whether subtotals have been created or not
        ' to FALSE. That way, the code in refresh_subtotal_ribbon will get it and
        ' set the variable bButtonClicked to false. That in turn means that ..... ???????
        Range("Subtotals_exist").Value = 0
        Call Ribbon_modules.refresh_subtotal_ribbon("")
      End If
    
      Application.ScreenUpdating = False
      Call import_new_data(1, "Koncernstructure.xlsx", sw_error)
      If sw_error = True Then
        Application.ScreenUpdating = True
        Exit Sub
      End If
      ' Remove any bold cells
      Columns("A:A").Select
      Selection.Font.Bold = False
      ' and ensure the outline is removed
      Selection.ClearOutline
      '
      ' Sort the columns as per "normal"
      Call sort_sheet1
      
      Range("A1").Select
    
      Application.ScreenUpdating = True
    
    End Sub
    What I'm having difficulty in getting into my head/understanding is what routines are run as a result of the call to
    Code:
    rxIRibbonUI.InvalidateControl ("Toggle_subtotals")
    in refresh_subtotal_ribbon.

    I'm assuming (since I now seem to have it working) that the rxlblFeedback_getLabel and rxbtnProcess_getImage are run, but is there any
    documentation where I can look to see if others are run ? (Out of interest, I tried adding a MSGBOX call at the start of both rxlblFeedback_getLabel/rxbtnProcess_getImage. All that happened was that the ribbon option disappeared completely as an option. Debug.print
    seemed to be okay though)

Posting Permissions

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