Ribbon checkbox onPress sub does not work if called from a generic onPress sub, toggle button does?

OaklandJim

Board Regular
Joined
Nov 29, 2018
Messages
72
Office Version
  1. 2019
Platform
  1. Windows
Ribboneers

I set up a generic onPress routine for the ribbon. It merely calls the element's onPress sub. See code below.

Works great with a toggle button. So I set it up to use with checkboxes too. I figger that there is an advantage to using that generic callback name in xml so it does not change even if the underling sub name does.

Anyway, that approach seems to not work with checkboxes. I have a checkbox-specific onPress callback sub. Works fine if I tell xml to use that sub for checkbox onPress callback. But, if I tell xml to use the generic callback (sub) the checkbox is not updated.

It seems that the ByRef "return value" param sent to my generic sub -- and passed along to the checkbox-specific sub -- does not get set by the checkbox-specific sub.

I must be missing something.

Shown below the code is immediate window output showing that the checkbox-specific sub fires when the generic one calls it but the return value param's value is not changed when accessed in the the generic sub.

VBA Code:
'       Generic GetPressed Sub

'In all cases, call the element-specific getPressed
'sub passing along the two ByRef params so the
'pvReturnedValue param can be set by the sub that is called.
'By convention, that sub will be named with the id followed
'by "_getPressed"

Sub GetPressed( _
   ByRef pControl As IRibbonControl, _
   ByRef pvReturnedValue As Variant)
  
Debug.Print Chr(10) & "Sub GetPressed, control = " & pControl.id & ", "
Debug.Print "call sub " & pControl.id & "_getPressed"

    Application.Run pControl.id & "_getPressed", pControl, pvReturnedValue

Debug.Print Chr(10) & "Sub GetPressed after calling element-specific onPress sub, pvReturnedValue = " & pvReturnedValue

End Sub


Sub T1G4Checkbox1_getPressed(pControl As IRibbonControl, ByRef pvReturnedVal As Variant)

Debug.Print Chr(10) & "Sub T1G4Checkbox1_getPressed, pControl.id = " & pControl.id

'   Set checked/unchecked (pressed) based on hidden status of a column
    If ThisWorkbook.Worksheets("Sheet1").Range("Header_ColTest").EntireColumn.Hidden _
     Then
        pvReturnedVal = True
Debug.Print "column is hidden, set control to true/checked. pvReturnedVal = " & pvReturnedVal

    Else
        pvReturnedVal = False
Debug.Print "column is NOT hidden, set control to false/unchecked. pvReturnedVal = " & pvReturnedVal

    End If

End Sub

>>> Debug.print output <<<

Sub GetPressed, control = T1G4Checkbox1,
call sub T1G4Checkbox1_getPressed

Sub T1G4Checkbox1_getPressed, pControl.id = T1G4Checkbox1
column is hidden, set control to true/checked. pvReturnedVal = True

Sub GetPressed after calling element-specific onPress sub, pvReturnedValue = False
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

OaklandJim

Board Regular
Joined
Nov 29, 2018
Messages
72
Office Version
  1. 2019
Platform
  1. Windows
Update. In the generic onPress sub I declared a boolean var and sent THAT to my element-specific onPress sub. Even though it is explicitly a ByRef parameter in the element-specific onPress sub the value is just not being returned to the generic onPress sub that makes the call. I'm too Excelame to know if Excel is not handling the ByRef param value change or if it is related to the ribbon. You'd think that declaring the local boolean var in the generic sub would enable passing of value back to the generic onPress sub from checkbox-specific onPress sub which seems like any other sub.

Code:
'Generic getPressed sub

Sub GetPressed( _
   ByRef pControl As IRibbonControl, _
   ByRef pvReturnedValue As Variant)
  
   Dim bIsPressed As Boolean
  
Debug.Print Chr(10) & "In Sub GetPressed, control = " & pControl.id & ", "
Debug.Print "call sub " & pControl.id & "_getPressed"

    Application.Run pControl.id & "_getPressed", pControl, bIsPressed
    
    pvReturnedValue = bIsPressed

Debug.Print Chr(10) & "Sub GetPressed after calling element-specific onPress sub"
Debug.Print "bIsPressed = " & bIsPressed & ", pvReturnedValue = " & pvReturnedValue

End Sub


'Checkbox-specific getPressed sub

Sub T1G4Checkbox1_getPressed(pControl As IRibbonControl, ByRef pbReturnedVal As Boolean) 'ByRef pvReturnedVal As Variant)

Debug.Print Chr(10) & "In Sub T1G4Checkbox1_getPressed, pControl.id = " & pControl.id

'   Set checked/unchecked (pressed) based on hidden status of a column
    If ThisWorkbook.Worksheets("Sheet1").Range("Header_ColTest").EntireColumn.Hidden _
     Then
        pbReturnedVal = True
Debug.Print "column is hidden, set control to true/checked. pbReturnedVal = " & pbReturnedVal

    Else
        pbReturnedVal = False
'Debug.Print "column is NOT hidden, set control to false/unchecked. pvReturnedVal = " & pvReturnedVal
Debug.Print "column is NOT hidden, set control to false/unchecked. pbReturnedVal = " & pbReturnedVal
    End If

End Sub


>>> Debug.Print output <<<

In Sub GetPressed, control = T1G4Checkbox1,
call sub T1G4Checkbox1_getPressed

In Sub T1G4Checkbox1_getPressed, pControl.id = T1G4Checkbox1
column is hidden, set control to true/checked. pbReturnedVal = True

Sub GetPressed after calling element-specific onPress sub
bIsPressed = False, pvReturnedValue = False
 

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
Looks like it's getting called by the click and not value 'changed' event which is most likely cause of value not being updated until after the sub. ORDER of EVENTS
 

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
Does not work like that...
 

OaklandJim

Board Regular
Joined
Nov 29, 2018
Messages
72
Office Version
  1. 2019
Platform
  1. Windows
Thnx for the post! I guess I need to read up a bit.

But like I said this approach works with toggle button and the same exact sub checkbox-specific onPress callback works if called directly.

And both the checkbox specific and the generic sub seem to fire correctly when the tab is refreshed, as I would expect.
 

OaklandJim

Board Regular
Joined
Nov 29, 2018
Messages
72
Office Version
  1. 2019
Platform
  1. Windows
Success! I used a global var to pass the value (to set status of the checkbox) to the onPress callback rather than using the callback 's normal ByRef param. I now have simple and a generic onPress sub that works well for checkboxes and toggle buttons.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,485
Messages
5,548,333
Members
410,828
Latest member
A9Bosv3
Top