PDA

View Full Version : Controlling checkbox using customdocumentproperty



Ken Puls
2011-04-08, 05:20 AM
This is for Gary, who posted on one of my RibbonX blog posts (http://www.excelguru.ca/blog/2006/11/29/modifying-the-ribbon-part-6/#comments).

Here's the RibbonX code, which creates a checkbox and a button on the XLG tab:


<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:

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, _
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.

jcabral
2018-03-02, 03:53 PM
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