Keep Check box Checked Userform

Question

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?

Answer 1

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