Custom Ribbon Checkbox, use checked/unchecked state in VBA?

[Nanclus]

I am trying to setup a custom ribbon in Excel using the Custom UI Editor. My goal is to have a checkbox in the ribbon for the user to check or uncheck, and I would like to read the state of the checkbox and use it in a "IF" statement in my VBA to decide if I want to execute some code or not.

As a simple example, in the Custom UI Editor within a ribbon, tab, group, I have:
<checkBox id = "checkboxShowMessage"
label = "Show Message">

Then in the VBA in a sub I have:
if checkboxShowMessage.CheckState = True then
Msgbox ("Message is shown")
end if

This is giving me an object not defined error. I don't know if I need to use CheckState, or IsChecked, or whatever, I see examples on the net with both. I also don't know if the "checkboxShowMessage" is a child in an Object such that I would need to do something like another example I saw that said RibbonControls.checkboxShowMessage, etc... I'm lost, Please help!

 

[Nanclus]

Seems my Custom UI Editor text got cut off. In the code I had:
<checkBox id = "checkboxShowMessage" label = "Show Message">

 

[Nanclus]

Third time is the charm... I think posting doesn't like the open close carrots
checkBox id = "checkboxShowMessage" label = "Show Message"

 

[Domenic]

Unfortunately, the board is interpreting what you posted as code, so we can't really see what you have.Try something like this...

XML Code

Note: Remove the space after ">" and the space before ">".

< checkBox 
                        id="checkboxShowMessage"
                        label="Checkbox1"
                        onAction="onAction"/ >

VBA Code

[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] onAction(control [COLOR=darkblue]As[/COLOR] IRibbonControl, pressed [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR])
[COLOR=green]'[/COLOR]
' Code for onAction callback. Ribbon control checkBox
[COLOR=green]'[/COLOR]
    [COLOR=darkblue]If[/COLOR] control.ID = "checkboxShowMessage" [COLOR=darkblue]Then[/COLOR]
        MsgBox "You " & IIf(pressed, "checked", "unchecked") & " " & control.ID & ".", vbInformation
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[Nanclus]

Hi Domenic,

So yes thanks, I see how the code you supplied would work. But in your case you are executing a sub onAction of checking the box. What I really need is the following. I have a huge sub already coded.... and about half way through, I just want to check to see if the box is checked or not, and if it is checked, execute XYZ. So really I don't want to execute a new sub on check... I just want the user to be able to check the box if they want, and be able to "read" if that box is checked or not whenever I want in another sub.

So I need something like....in the middle of an already running sub.... if checkboxShowMessage is checked then .... XYZ.... end if. How would I code that?

Public Sub alreadyCodedSub()

'Dummy stuff already doing stuff
Test1 = 1 + 2
Test2 = Test1+7
Test3 = Test2 + Test1

If checkboxShowMessage = True then
     MsgBox ("The answer is" & Test3)
end if

End Sub

Thanks so much!

 

[Domenic]

In that case, I think we can do it this way. We can designate a cell within a worksheet to keep track of the checkbox status. So the cell would contain True if checked, and False if unchecked. This way, when we open/re-open the workbook, we'll know the status of the checkbox when it was last saved, and change the state of the checkbox accordingly. And this cell will change as the checkbox is checked/unchecked, so that we can reference it at any time in any other code. So if we designate cell A1 in Sheet1 as our cell, we can do the following...

XML Code

Note: A space has been added after each occurrence of "<" so that the Board doesn't interpret it as code. A space has also been added between both occurrences of "on" and "Load" for the same reason. Therefore, make sure that you get rid of these spaces before running the macro.

< customUI 
    xmlns="http://schemas.microsoft.com/office/2006/01/customui"
    on Load="on Load">
    < ribbon >
        < tabs >
            < tab 
                id="Tab1"
                label="Tab1">
                < group 
                    id="Group1"
                    label="Group1">
                    < checkBox 
                        id="checkboxShowMessage"
                        label="Checkbox1"
                        getPressed="getPressed"
                        onAction="onAction"/>
                    < checkBox 
                        id="checkboxShowMessage2"
                        label="Checkbox2"
                        getPressed="getPressed"
                        onAction="onAction"/>
                < /group >
            < /tab >
        < /tabs >
    < /ribbon >
< /customUI >

VBA Code

In the code module for ThisWorkbook...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_Open()
    [COLOR=darkblue]With[/COLOR] Worksheets("Sheet1").Range("A1")
        [COLOR=darkblue]If[/COLOR] .Value = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
            b_checkboxShowMessage = [COLOR=darkblue]True[/COLOR]
        [COLOR=darkblue]Else[/COLOR]
            b_checkboxShowMessage = [COLOR=darkblue]False[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

In a regular module...

[COLOR=darkblue]Public[/COLOR] b_checkboxShowMessage [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR]

[COLOR=darkblue]Dim[/COLOR] MyRibbon [COLOR=darkblue]As[/COLOR] IRibbonUI

[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] ******(ribbon [COLOR=darkblue]As[/COLOR] IRibbonUI)
[COLOR=green]'[/COLOR]
' Code for ****** callback. Ribbon control customUI
[COLOR=green]'[/COLOR]
    [COLOR=darkblue]Set[/COLOR] MyRibbon = ribbon
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] getPressed(control [COLOR=darkblue]As[/COLOR] IRibbonControl, [COLOR=darkblue]ByRef[/COLOR] returnedVal)
[COLOR=green]'[/COLOR]
' Code for getPressed callback. Ribbon control checkBox
[COLOR=green]'[/COLOR]
    [COLOR=darkblue]If[/COLOR] control.ID = "checkboxShowMessage" [COLOR=darkblue]Then[/COLOR]
        returnedVal = b_checkboxShowMessage
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] onAction(control [COLOR=darkblue]As[/COLOR] IRibbonControl, pressed [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR])
[COLOR=green]'[/COLOR]
' Code for onAction callback. Ribbon control checkBox
[COLOR=green]'[/COLOR]
    [COLOR=darkblue]If[/COLOR] control.ID = "checkboxShowMessage" [COLOR=darkblue]Then[/COLOR]
        b_checkboxShowMessage = pressed
        Worksheets("Sheet1").Range("A1").Value = pressed
    End [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

Then you can check on the status of the checkbox as follows...

[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] alreadyCodedSub()
    [COLOR=darkblue]If[/COLOR] b_checkboxShowMessage = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR]
         MsgBox ("The answer is" & Test3)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[Nanclus]

Awesome Domenic, this would definitely work. Let me ask though how would I directly check the state of the checkbox. Say if I didn't use a cell to store the value... cut down on the complexity maybe. Thanks so much!

 

[RoryA]

You have to store it somewhere - be it a cell, name, or variable. You can't simply read it directly.

 

[Domenic]

I see that when I posted the VBA code for "on Load", I missed putting a space between "on" and "Load" so that the Board wouldn't interpret it as code. And so it got messed up. In any case, the "on Load" callback isn't needed. So you can omit it from both the XML and VBA code.

 

[glda19]

At what time we comme in the macro getpressed