CODEWITHSUNDEEP
excelvbacheckboxuserform
Drew Bennett
Drew Bennett

Reputation: 483

Keep Check box Checked Userform

Currently, I have created a user form with three check boxes. These check boxes activate modules I have created. When I check the box in the user form I want the box to stay checked or unchecked. Any thoughts?

Upvotes: 0

Views: 3388

Answers (2)

curious
curious

Reputation: 1500

As long as the spreadsheet remains open, Me.Hide instead of Unload Me will do the trick, but once closed, the spreadsheet won't be able to remember the settings any more.

If you want the spreadsheet to remember the settings, even if it's closed, the selection settings need to be captured somewhere.

Let's say we created a sheet named "chkboxVal". We can then store the data there through Ok_Click event/procedure, and get the data back through the UserForm_Initialize() section.

The code will look like this:

Private Sub Ok_Click()
    Dim ctrl As Control, i As Integer
    For Each ctrl In UserForm1.Controls
       If TypeName(ctrl) = "CheckBox" Then
           i = i + 1
           Sheets("chkboxVal").Cells(i, 1) = ctrl.Value
       End If
    Next
    Unload Me
End Sub
Private Sub UserForm_Initialize()
    Dim ctrl As Control, i As Integer
    For Each ctrl In UserForm1.Controls
       If TypeName(ctrl) = "CheckBox" Then
           i = i + 1
           ctrl.Value = Sheets("chkboxVal").Cells(i, 1)
       End If
    Next
End Sub

You might also want hide the "chkboxVal" sheet in a way that users aren't able to unhide it. If so, just add this line in the module.

Sheets("chkboxVal").Visible = xlVeryHidden

Upvotes: 3

matt
matt

Reputation: 1

Make sure you don't have an "Unload Me" or "Unload Userform#" before you are trying to call the modules. This will reset the userform to the default.

You can also change the default (check/unchecked) for each checkbox. While building the userform, click on the checkbox, and in theproperties window, select if you want it to be set as true or false as the default.

Upvotes: 0

Related Questions