Checkbox Loose Value when Closing sheet

[Rocksie]

Hi All,

I need some help please. I have active X checkboxes on a form which when unchecked (the default is checked) will hide certain rows based a the value in a cell in column "L".

My problem is when I close the form with the "X" on top or when I save the file and re-open it, the checkbox looses its value, i.e when it was unchecked before, it will be checked when opening the file again, BUT the rows will still be hidden which creates problems...

Any ideas how to overcome that?

Thanks!

 

[jonathann3891]

The following example will store all checkbox values as names, then restore when form is re-initialized.

[/COLOR][COLOR=#333333]Private Sub cmd_Save_Click()[/COLOR]
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">  For Each ct In Controls
    If TypeName(ct) = "CheckBox" Then ThisWorkbook.Names.Add ct.Name, ct
  Next

  Unload
End Sub

Private Sub UserForm_Initialize()
  On Error Resume Next
  For Each ct In Controls
    If TypeName(ct) = "CheckBox" Then ct.Value = ThisWorkbook.Names(ct.Name).RefersTo = "=TRUE"
  Next </code>[COLOR=#333333]End Sub[/COLOR][COLOR=#333333]

 

[Rocksie]

Thanks Jonathann, I'll try it.

Do I put it in the worksheet module or in the Userform module?

 

[mikerickson]

Userform controls lose their value when the form is closed.
But, since these checkboxes are linked to the visibility of rows, you could write your initialize event to look at those rows and determine which of the checkboxes should be checked.

 

[Rocksie]

Thank you all for the advice. I've added a very hidden sheet to the workbook to which the checkbox reads a value, that solved my problem