Custom ribbon checkboxes do not retain values when file is closed.

**ericsastud** · 10-28-2010, 06:06 PM

I have a group of checkboxes on a custom ribbon that I use to hide certain worksheets. When the file is closed and reopened, the checkboxes reset to their default value of false. I would like the checkboxes to retain the values they had when I closed the file.

How do I change the values of the checkboxes using vba? Thanks in advance!

**MarvinP** · 10-28-2010, 06:36 PM

Hi Eric,

I'd say you need to save the status of the check boxes somewhere and reset them to this saved value when you first open the workbook. I'd need an example file to examine how and where this should be done.

**ericsastud** · 10-28-2010, 07:54 PM

Here is an example file. Look on the Inputs and Ratios Tab on the ribbon. The checkboxes show/hide sheets. If have Sheet 1 and Sheet 3 are visible when the file is opened, how do I make Checkbox 1 and Checkbox 3 be checked?

**MarvinP** · 10-29-2010, 01:06 AM

Hi eric,

I can't seem to find a way to make those boxes checked or not. It looks like the Ibuttoncontrol is a Office Object and is read only. I can read the Tag and Id of the control but it doesn't look like it is checkable using Excel VBA code. A mouse click seems to be the only way I can get them to change.

On http://msdn.microsoft.com/en-us/libr...oncontrol.aspx it claims it is not supported using VBA. I think that is the final answer.

I wonder if other programmers have an answer.

**romperstomper** · 10-29-2010, 04:04 AM

You need to set up a callback function for the getPressed attribute of each checkbox that returns true or false based on the relevant sheet visibility.

**romperstomper** · 10-29-2010, 05:02 AM

See revised attachment for example - you will need to check the new CustomUI XML too.

**royUK** · 10-29-2010, 05:12 AM

Nice work Romper.

I think the actual code could be a little shorter

Option Explicit
Sub ShowSheet1(control As IRibbonControl, pressed As Boolean)
On Error GoTo err_exit
        Sheets("Sheet1").Visible = pressed
        Exit Sub
err_exit:
        err_msg
End Sub

Sub ShowSheet2(control As IRibbonControl, pressed As Boolean)
On Error GoTo err_exit
    Sheets("Sheet2").Visible = pressed
    Exit Sub
err_exit:
        err_msg
End Sub

Sub ShowSheet3(control As IRibbonControl, pressed As Boolean)
On Error GoTo err_exit
 Sheets("Sheet3").Visible = pressed
 Exit Sub
err_exit:
        err_msg
End Sub
'Callback for CheckBox1 getPressed
Sub cbxGetEnabledState(control As IRibbonControl, ByRef returnedVal)
    Select Case control.ID
        Case "CheckBox1"
            returnedVal = Sheet1.Visible = xlSheetVisible
        Case "CheckBox2"
            returnedVal = Sheet2.Visible = xlSheetVisible
        Case "CheckBox3"
            returnedVal = Sheet3.Visible = xlSheetVisible
    End Select
End Sub

Sub err_msg()
MsgBox "One sheet must always be visible", vbCritical, "Error alert"
End Sub