Simulate click on user defined ribbon button

misi01

New member
Joined
Jul 13, 2011
Messages
31
Reaction score
0
Points
0
Location
Stockholm,Sweden
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.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,522
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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.)
 

misi01

New member
Joined
Jul 13, 2011
Messages
31
Reaction score
0
Points
0
Location
Stockholm,Sweden
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)
 

misi01

New member
Joined
Jul 13, 2011
Messages
31
Reaction score
0
Points
0
Location
Stockholm,Sweden
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. [COLOR=#ff0000][B]That in turn means that ..... ???????[/B][/COLOR]
    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)
 
Top