Excel VBA Userform read current values when opening

Question

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.

Could you edit your question to include an example of what you have tried so far? — Robert Todar
– Robert Todar, Commented Jun 19, 2019 at 14:31
Relevant - if you separate the data from the form code, and encapsulate this data in its own "model" class, you can have a Public Function Create(ByVal model As ColumnState) As Hider factory method in the form - then instead of Hider.Show you would have With Hider.Create(myModel) ....Show... End With, where myModel holds the "hidden state" for each column, and the Activate handler can initialize control values from model properties. — Mathieu Guindon
– Mathieu Guindon, Commented Jun 19, 2019 at 14:48
Or just treat everything as global, and have the form pull the data where it's at in the Initialize or Activate handler, like below. — Mathieu Guindon
– Mathieu Guindon, Commented Jun 19, 2019 at 14:49
FYI In addition to Mathieu's valid comment how to professionalize (Userform) coding applying MVP (Model-Viewer-Presenter) pattern logic to VBA, you could find further readings listed at an article how to Destroy a modeless Userform instance properly. — T.M.
– T.M., Commented Jun 20, 2019 at 12:27

T.M. · Accepted Answer · 2019-06-19 14:55:55Z

1

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

edited Jun 19, 2019 at 14:55

answered Jun 19, 2019 at 14:45

T.M.

10k3 gold badges40 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mathieu Guindon Over a year ago

Simpler: Me.Week3.Value = ActiveSheet.Range("N:Q").Columns.Hidden - no need to compare a Boolean expression with a Boolean

T.M. Over a year ago

@MathieuGuindon - Edited answer due to your comment, adding just the negation particle Not :-)

Robin Walter Over a year ago

This answer worked! However it wouldn't sync automatically when opening the userform for some reason, but after creating an 'sync' button it works perfectly.

T.M. Over a year ago

Glad my answer helped. BTW Suggest to fully qualify your range references as a rule, e.g. via ThisWorkbook.Worksheets("MySheetName").Range("N:Q").Columns.Hidden or via the sheet's codename (e.g. Sheet1.Range(…)…

Collectives™ on Stack Overflow

Excel VBA Userform read current values when opening

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related