Controlling checkbox using customdocumentproperty

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,531
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
This is for Gary, who posted on one of my RibbonX blog posts.

Here's the RibbonX code, which creates a checkbox and a button on the XLG tab:
Code:
<customUI onLoad="OnLoad" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
 
    <ribbon startFromScratch="false">
        <tabs>
            <tab id = "xlgTab" label="XLG" insertAfterMso="TabView">
                <group id="TestTab" label="My Testing Tab">
                    <checkBox id="chkbox1" label="MyCheckbox" onAction="chkBoxControl" getPressed="GetPressed" />
                    <button id="button1" label="Peek-a-boo" onAction="btnControl" getVisible="GetVisible" imageMso="HappyFace" />
                </group>
            </tab>
        </tabs>
    </ribbon>
 
</customUI>

The VBA code in the file is divided into two parts.

Standard module:
Code:
Option Explicit
Private Sub OnLoad(ribbon As IRibbonUI)
'Set the RibbonUI to a workbook property for later use
    ThisWorkbook.ribbonUI = ribbon
End Sub
 
Private Sub GetPressed(ByVal control As IRibbonControl, ByRef returnID)
'Query the property for the value of the chkbox, and feed it back
'to the ribbon so that it can be set appropriately
    If control.ID = "chkbox1" Then returnID = ThisWorkbook.chkBox1
End Sub
 
Private Sub GetVisible(control As IRibbonControl, ByRef returnedVal)
'Update the visibility of the button based on the checkbox value
'(the checkbox should always be visible, so no need to set it each time)
    If control.ID = "button1" Then returnedVal = ThisWorkbook.chkBox1
End Sub
 
Private Sub chkBoxControl(control As IRibbonControl, pressed As Boolean)
'Toggle the state of the controls
 
    With ThisWorkbook
        .chkBox1 = pressed
        .ribbonUI.InvalidateControl "button1"
    End With
End Sub
 
Private Sub btnControl(control As IRibbonControl)
'Give the user some kind of feedback that they clicked the button
    MsgBox "You got me!"
End Sub

And the following code, which contains the CustomDocumentProperty, goes in the Thisworkbook module:
Code:
Option Explicit
'Private variables to hold state of Ribbon and Ribbon controls
Private pRibbonUI As IRibbonUI
 
Public Property Let ribbonUI(iRib As IRibbonUI)
'Set RibbonUI to property for later use
    Set pRibbonUI = iRib
End Property
 
Public Property Get ribbonUI() As IRibbonUI
'Retrieve RibbonUI from property for use
    Set ribbonUI = pRibbonUI
End Property
 
Public Property Let chkBox1(b As Boolean)
    'Set value of chkBox1 state property
    Dim DocProps As CustomProperties
    'Save chkBox1 value in a customdocument property
    On Error Resume Next
    ThisWorkbook.CustomDocumentProperties("chkBox1") = b
    'If customdocument property did not exist, create it
    If Err.Number <> 0 Then
 
        ThisWorkbook.CustomDocumentProperties.Add _
            Name:="chkBox1", _
            LinkToContent:=False, _
            Type:=msoPropertyTypeBoolean, _
            Value:=b
    End If
    On Error GoTo 0
 
End Property
 
Public Property Get chkBox1() As Boolean
    'Read value of chkBox1 state property
    Dim DocProps As CustomProperties
 
    'Save chkBox1 value in a customdocument property
    On Error Resume Next
    chkBox1 = ThisWorkbook.CustomDocumentProperties("chkBox1").Value
    'If customdocument property did not exist, create it
    If Err.Number <> 0 Then
        chkBox1 = False
    End If
    On Error GoTo 0
 
End Property

To be fair, I've also set up properties in the ThisWorkbook module, but that was I can refer to ThisWorkbook.chkBox1 and ThisWorkbook.ribbonUI as well.

The CustomDocumentProperty write sthe value of the checkbox into a document property that is saved with the workbook. So if you check the checkbox, the button will show up. Save and close the workbook, and when you re-open it, the checkbox will be checked and the button showing.

I personally prefer this to saving data in cells as it's much more difficult for the user to accidentally delete. It also goes with the workbook which can be handy. Saving things in the registry, on the other hand, keeps it out of the workbook, but means that the settings stay with the user.
 

Attachments

  • CheckboxTest.xlsm
    23.9 KB · Views: 734
Hi

How can I do this type of procedure but for a DropDown type control, that is, if I have a dynamically loaded list of names for the DropDown control with a given Sort, how can I keep this type if I save and reopen the workbook?

Thanks
 
Back
Top