Results 1 to 2 of 2

Thread: Controlling checkbox using customdocumentproperty

  1. #1
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider

    Controlling checkbox using customdocumentproperty

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

    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:
    <customUI onLoad="OnLoad" xmlns="">
        <ribbon startFromScratch="false">
                <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" />
    The VBA code in the file is divided into two parts.

    Standard module:
    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:
    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, _
        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.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!

    Main Site: -||- Blog: -||- 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.

  2. #2
    Neophyte jcabral's Avatar
    Join Date
    Oct 2017
    Excel Version
    Microsoft Excel 2013

    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?


Posting Permissions

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