1

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.

Steffen Moritz
  • 7,277
  • 11
  • 36
  • 55
Robin Walter
  • 13
  • 3
  • Could you [edit](https://stackoverflow.com/posts/56669744/edit) your question to include an example of what you have tried so far? – Robert Todar Jun 19 '19 at 14:31
  • 3
    [Relevant](https://stackoverflow.com/questions/47288496/are-there-disadvantages-in-putting-code-into-userforms-instead-of-modules/47291028#47291028) - 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 Jun 19 '19 at 14:48
  • 1
    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 Jun 19 '19 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](https://stackoverflow.com/questions/47357708/vba-destroy-a-modeless-userform-instance-properly). – T.M. Jun 20 '19 at 12:27

1 Answers1

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
T.M.
  • 9,436
  • 3
  • 33
  • 57
  • 2
    Simpler: `Me.Week3.Value = ActiveSheet.Range("N:Q").Columns.Hidden` - no need to compare a Boolean expression with a Boolean – Mathieu Guindon Jun 19 '19 at 14:50
  • @MathieuGuindon - Edited answer due to your comment, adding just the negation particle `Not` :-) – T.M. Jun 19 '19 at 14:57
  • 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. – Robin Walter Jun 20 '19 at 06:22
  • 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(…)…` – T.M. Jun 20 '19 at 12:02