CODEWITHSUNDEEP
excelvbauserform
Robin Walter
Robin Walter

Reputation: 13

Excel VBA Userform read current values when opening

I am trying to make an user form for hiding/showing different columns of the worksheet.

So i've made a button which opens the userform (called 'hider') with the following code:

Private Sub CommandButton1_Click()
Hider.Show
End Sub

The userform than currently contains two checkboxes which hide the selected columns using:

Private Sub Week3_Click()
Range("N:Q").Columns.Hidden = Not Week3
End Sub

So if the checkbox is 'checked' the columns are shown and if 'unchecked' the columns are hidden, this part works, except every time the userform opens it will reset the checkboxes to their native state of 'unchecked' while the columns remain hidden (which is ok).

So my question is:

How can I sync the checkboxes in the userform to the currently active value of the columns? I was thinking about making a sync button on the userform or an action to read all the current values when opening the userform, but i couldn't get that to work.

Upvotes: 1

Views: 271

Answers (1)

T.M.
T.M.

Reputation: 9948

If I understand your question correctly ("...or an action to read all the current values when opening the userform") just use the following code within the Userform code module to synchronize your checkbox when opening: 

Private Sub UserForm_Initialize()

'Me.Week3.Value = IIf(ActiveSheet.Range("N:Q").Columns.Hidden = True, False, True)
Me.Week3.Value = Not ActiveSheet.Range("N:Q").Columns.Hidden  ' simplified due to comment thx Mathieu Guindon

' ...
End Sub

Upvotes: 1

Related Questions