Userform - Checkbox - keeping the checkmark saved

[maclachlan]

Hopefully somebody can help me out.
I have setup a userform with various checkboxes included on the form.
The checkbox's hide and unhide the various columns. The code I use
for this is:

Private Sub CheckBox4_Click()
If CheckBox4.Value Then
Columns("D").Hidden = True
Else
Columns("D").Hidden = False
End If
End Sub

Everything works fine until I exit the userform and then re-open it.
The checkmarks on the userform are gone but the column is still hidden
My question is how do I save the settings of the checkbox once th
userform is re-opened. Is there an easier way to code the checkboxe
as I have about 20 of them. I'm new to the VB coding so anything wil
help.
Thanks
Ro

 

[spinner]

you can try add this to the check box:

If CheckBox1.Value = True Then
Worksheets("sheet16").Range("C2") = "Check"
Else: Worksheets("sheet16").Range("C2") = ""
End If

 

[JE McGimpsey]

Couple of techniques I use:

1) You don't need to unload userforms in the middle of an application -
hide them instead and they'll retain all their settings. This is
probably the best option if you don't need to save values from session
to session.


2) Create a hidden sheet to hold your settings. Then in the userform
Initialize code, read those values in:

Private Sub Initialize()
Dim vCBArray as Variant
With ThisWorkbook.Sheets("MyHiddenSheet").Range("A1:A20")
Checkbox1.Value = .Item(1).Value
Checkbox2.Value = .Item(2).Value
'..
End Sub

When the userform exits, reverse the process in the userform's Terminate
event macro.

 

[Tom Ogilvy]

You have the information you need - just loop through the columns and if the
the column is hidden, check the appropriate checkbox. You can do this in
the userform Initialize event.

 

[maclachlan]

Thanks for the replies
I have a question from JE McGimpsey's solution
This is what I have so far when the userform opens. The Initializ
code never seems to run when I click on the Preffrm button?
From my little experience I believe the code is setup to return a valu
(1 or 0) in the column A of the Hidden Sheet depending of the true o
false value of the checkbox. This value is then read into the userfor
the next time it is opened.

How does the code tell what position to put the Value of each checkbo
on the Hidden sheet As per the code CheckBox1.Value = .Item(1).Value.

This is what I have so far.

Private Sub Preffrm_Initialize()
Dim vCBArray As Variant
With ThisWorkbook.Sheets("MyHiddenSheet").Range("A1:A20")
CheckBox1.Value = .Item(1).Value
CheckBox2.Value = .Item(2).Value
End With
End Sub

Private Sub Preffrm_Click()
Dim frm As New Hide
frm.Show
End Sub

Thanks agai

 

[JE McGimpsey]

This works for me in the userform's code module (don't replace Userform
with your form's name):

Private Sub UserForm_Initialize()
With ThisWorkbook.Sheets("MyHiddenSheet").Range("A1:A20")
CheckBox1.Value = .Item(1).Value
CheckBox2.Value = .Item(2).Value
End With
End Sub

Private Sub UserForm_Terminate()
With ThisWorkbook.Sheets("MyHiddenSheet").Range("A1:A20")
.Item(1).Value = CheckBox1.Value
.Item(2).Value = CheckBox2.Value
End With
End Sub

..Item(1) is the first item in the range A1:A20, or A1.

 

[Harald Staff]

Tom's right, the columns themselves remember what your values should be. See
if this works for you:

Private Sub UserForm_Initialize()
CheckBox1.Value = Columns(1).Hidden
CheckBox2.Value = Columns(2).Hidden
End Sub

Private Sub CheckBox1_Click()
Columns(1).Hidden = CheckBox1.Value
End Sub

Private Sub CheckBox2_Click()
Columns(2).Hidden = CheckBox2.Value
End Sub

HTH. Best wishes Harald

 

[JE McGimpsey]

On reading Tom's reply, and rereading your post, I misunderstood your
question. If you want the checkboxes to reflect the actual state of the
column's hidden property, just check that property in the initialize
event:

Private Sub UserForm_Initialize()
With Sheets("Sheet1")
CheckBox1.Value = .Columns(1).Hidden
Checkbox2.Value = .Columns(2).Hidden
'...
End With
End Sub

 

[maclachlan]

Thanks for the help that worked great.
The only problem I have now is when I close the workbook and re-open i
the check mark still disappear. This only happens when I totally clos
the workbook. If I close the userform and re-open the check marks ar
still there. Am I missing some code somewhere.
Thanks

Here is what I have so far

Private Sub RepPref_Initialize()
CheckBox1.Value = Columns(1).Hidden
CheckBox2.Value = Columns(2).Hidden
CheckBox3.Value = Columns(3).Hidden
CheckBox4.Value = Columns(4).Hidden
CheckBox5.Value = Columns(5).Hidden
CheckBox6.Value = Columns(6).Hidden
CheckBox7.Value = Columns(7).Hidden
CheckBox8.Value = Columns(8).Hidden
CheckBox9.Value = Columns(9).Hidden
CheckBox10.Value = Columns(10).Hidden
CheckBox11.Value = Columns(11).Hidden
CheckBox12.Value = Columns(12).Hidden
CheckBox13.Value = Columns(13).Hidden
CheckBox14.Value = Columns(14).Hidden
CheckBox15.Value = Columns(15).Hidden
CheckBox16.Value = Columns(16).Hidden
CheckBox17.Value = Columns(17).Hidden
CheckBox18.Value = Columns(18).Hidden
CheckBox19.Value = Columns(19).Hidden
CheckBox20.Value = Columns(20).Hidden
CheckBox21.Value = Columns(21).Hidden

End Sub


Private Sub CheckBox1_Click()
If CheckBox1.Value Then
Columns("A").Hidden = True
Else
Columns("A").Hidden = False
End If
End Sub
... The above section repeated for each CheckBox


Private Sub Preffrm_Click()
RepPref.Show

My main goal is to have a userform that non-excel user can use to hid
and unhide columns. I want them to be able to open the userform an
see what columns are already hidden

 

[Tom Ogilvy]

Private Sub RepPref_Initialize()

should be

Private Sub Userform_Initialize()

and it should be in the userform module for RepPref

regardless of the name of the userform, the initialize event is named
Userform_Initialize.

of course that begs the question of why you say it even works. You must be
hiding the form rather than unloading it.

 

[maclachlan]

Thanks for the help
When I make the changes to the userform module to this
Private Sub Userform_Initialize()
I get the following error.

Run-time error '1004'
Application-defined or object-defined error

The coding I'm using for the command button is
Private Sub Preffrm_Click()
RepPref.Show
End Sub
which is part of Sheet 1


Please help! I'm really close to getting this whole spreadsheet to com
together

 

[Tom Ogilvy]

I made a useform, named it RepPref. I put a commandbutton on a sheet and
named it Preffrm. the userform had 21 checkboxes. (does yours have 21
checkboxes, named CheckbBox1 to CheckBox21? )

I put your code in the sheet moudle

Private Sub Preffrm_Click()
RepPref.Show
End Sub

In the userform module, I placed this code:


Private Sub UserForm_Initialize()

CheckBox1.Value = Columns(1).Hidden
CheckBox2.Value = Columns(2).Hidden
CheckBox3.Value = Columns(3).Hidden
CheckBox4.Value = Columns(4).Hidden
CheckBox5.Value = Columns(5).Hidden
CheckBox6.Value = Columns(6).Hidden
CheckBox7.Value = Columns(7).Hidden
CheckBox8.Value = Columns(8).Hidden
CheckBox9.Value = Columns(9).Hidden
CheckBox10.Value = Columns(10).Hidden
CheckBox11.Value = Columns(11).Hidden
CheckBox12.Value = Columns(12).Hidden
CheckBox13.Value = Columns(13).Hidden
CheckBox14.Value = Columns(14).Hidden
CheckBox15.Value = Columns(15).Hidden
CheckBox16.Value = Columns(16).Hidden
CheckBox17.Value = Columns(17).Hidden
CheckBox18.Value = Columns(18).Hidden
CheckBox19.Value = Columns(19).Hidden
CheckBox20.Value = Columns(20).Hidden
CheckBox21.Value = Columns(21).Hidden

End Sub

Since Sheet1, the sheet with the button is the active sheet, columns(n)
refers to the activesheet.

Anyway, it worked fine for me.

 

[Tom Ogilvy]

change
named CheckbBox1 to CheckBox21?

to

named CheckBox1 to CheckBox21?

--
Regards,
Tom Ogilvy

I made a useform, named it RepPref. I put a commandbutton on a sheet and
named it Preffrm. the userform had 21 checkboxes. (does yours have 21
checkboxes, named CheckbBox1 to CheckBox21? )

I put your code in the sheet moudle

Private Sub Preffrm_Click()
RepPref.Show
End Sub

In the userform module, I placed this code:


Private Sub UserForm_Initialize()

CheckBox1.Value = Columns(1).Hidden
CheckBox2.Value = Columns(2).Hidden
CheckBox3.Value = Columns(3).Hidden
CheckBox4.Value = Columns(4).Hidden
CheckBox5.Value = Columns(5).Hidden
CheckBox6.Value = Columns(6).Hidden
CheckBox7.Value = Columns(7).Hidden
CheckBox8.Value = Columns(8).Hidden
CheckBox9.Value = Columns(9).Hidden
CheckBox10.Value = Columns(10).Hidden
CheckBox11.Value = Columns(11).Hidden
CheckBox12.Value = Columns(12).Hidden
CheckBox13.Value = Columns(13).Hidden
CheckBox14.Value = Columns(14).Hidden
CheckBox15.Value = Columns(15).Hidden
CheckBox16.Value = Columns(16).Hidden
CheckBox17.Value = Columns(17).Hidden
CheckBox18.Value = Columns(18).Hidden
CheckBox19.Value = Columns(19).Hidden
CheckBox20.Value = Columns(20).Hidden
CheckBox21.Value = Columns(21).Hidden

End Sub

Since Sheet1, the sheet with the button is the active sheet, columns(n)
refers to the activesheet.

Anyway, it worked fine for me.