Loop repeated code to set userform checkbox values

Question

I've a dashboard that uses a userform box field with checkboxes to choose what data to display on a chart.

My code is very copy and pasted.

Private Sub CommandButton21_Click()
UserForm1.Show
If Worksheets("Data Directorate").Range("X4").Value = True Then UserForm1.CheckBox1 = True
If Worksheets("Data Directorate").Range("X5").Value = True Then UserForm1.CheckBox2 = True
If Worksheets("Data Directorate").Range("X6").Value = True Then UserForm1.CheckBox3 = True

Is there a way to use a loop to do this?

I've more repeated code later on:

Private Sub CheckBox1_Click()
  Select Case CheckBox1.Value
  Case True
  Worksheets("Data Directorate").Range("X4").Value = True
  Case False
  Worksheets("Data Directorate").Range("X4").Value = False
End Select
End Sub

This repeats for 24 check boxes. Would it be possible to loop this?

basodre · Accepted Answer · 2015-05-28 13:18:19Z

2

All great advice posted in this thread, so I'd like to add something that can maybe help to simplify your loops. Controls have a Tag property which, as best I can tell, does nothing other than to store additional information about the control.

Using this to our advantage, we can include the linked cell in the Tag property for each checkbox. (For example, enter X4 into the Tag for linkage to cell X4). This permits for less of the information to be hardcoded, which makes it more adaptable.

Finally, the code would look like this.

Private Sub UserForm_Click()
    For Each octrl In Me.Controls
        If TypeName(octrl) = "CheckBox" Then
            Sheet1.Range(octrl.Tag).Value = octrl.Value
        End If

    Next octrl
End Sub

answered May 28, 2015 at 13:18

basodre

5,7901 gold badge18 silver badges25 bronze badges

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

6 Comments

vacip Over a year ago

Wow, very nice, I have never thought of this! Thanks!

FreeMan Over a year ago

Excellent! Never knew this existed! I would throw some error checking around it, as there's no guarantee the form editor (person) would remember to set every CheckBox's .tag property - especially if it's being modified at some point in the future...

basodre Over a year ago

@FreeMan I definitely agree. I usually expect that the poster will take the advice and develop it as necessary. Error checking is a must otherwise an empty tag will throw an exception.

Pat Searle Over a year ago

@FreeMan Sorry I'm new to this, what do you mean by some "error checking" and a CheckBox's 'tag' property

FreeMan Over a year ago

@PatSearle, this answer relies on you setting the .tag property of the checkbox = to the cell that stores its checked value. Since it relies on you (or someone maintaining after you) to manually set this up, it's worth writing some code around the statements to ensure that .tag has actually been set to a usable value before trying to use it. i.e. make sure you have a useable value before trying to use it.

|

BrakNicku · Accepted Answer · 2015-05-28 13:29:24Z

1

My approach in this scenario would be:

Set ControlSource property of the checkboxes to appropriate cells, leave only: UserForm1.Show

enter image description here

answered May 28, 2015 at 13:29

BrakNicku

6,0313 gold badges28 silver badges40 bronze badges

1 Comment

Pat Searle Over a year ago

Thank you, that's so much simpler than coding! I had no idea what ControlSource was. Thanks for the help.

FreeMan · Accepted Answer · 2015-05-28 13:19:46Z

1

One thing to simplify: instead of using If statements, just make the two sides equal. Like this:

Private Sub CommandButton21_Click()
UserForm1.Show

UserForm1.CheckBox1 = Worksheets("Data Directorate").Range("X4").Value

And the other one:

Private Sub CheckBox1_Click()
  Worksheets("Data Directorate").Range("X4").Value = CheckBox1.Value
End Sub

I recommend using the change event instead of the click event. Some user might use Tab and Space, then the click event won't trigger. Like this:

Private Sub CheckBox1_Change()
...

About looping through the checkboxes in the CommandButton21_Click event, that depends on the order of your checkboxes and your table. This might work, but you will have to try it on your table (the order of the checkboxes compared to the order of your cells can ruin the game...)

Dim contr As control
dim i as integer
i=4
For Each contr In UserForm1.Controls
  If TypeName(contr) = "CheckBox" Then
    contr.Value = cells(i,24).Value 'column 24 is column X
    i=i+1
  End If
Next

A possible variation to work when CheckBoxes are not in the expected order or get added/removed:

Dim contr As control
Dim J as Integer
Dim Offset as Integer

Offset = 3  'set this to the difference between 1 and the first row containing data

For Each contr In UserForm1.Controls
  If TypeName(contr) = "CheckBox" Then
    'set j to the checkboxes "number"
    J = cInt(right(contr.name, len(contr.name) - len("CheckBox")))
    'use the checkbox number + offset to find the row we want
    contr.Value = cells(J + Offset,24).Value 'column 24 is column X
  End If
Next

Hope this helps.

edited May 28, 2015 at 13:19

FreeMan

5,7092 gold badges32 silver badges58 bronze badges

answered May 28, 2015 at 13:01

vacip

5,4563 gold badges29 silver badges56 bronze badges

4 Comments

FreeMan Over a year ago

Your last loop is clever, but very dependent on control order! While it may work the first time, any future changes to the form could break it.

vacip Over a year ago

Yes, but that is basically true to any loop trying to iterate checkboxes. That is why I wouldn't recommend using it. Just go with the copy-paste, withouth If statements.

FreeMan Over a year ago

Cool... You mentioned it, I was just trying to emphasize it for the newbies. I'm going to edit in a change that will work for this particular question (and others with slight modification). Feel free to remove the edit if you don't like it.

basodre Over a year ago

@FreeMan and vacip. I like to use the Tag property to link the cell directly to the control, rather than linking them in a hardcoded manner in the actual source code. See my solution below for an alternative option.

FreeMan · Accepted Answer · 2015-05-28 12:54:22Z

0

For the second portion of your question:

Private Sub CheckBox1_Click()
  Worksheets("Data Directorate").Range("X4").Value = CheckBox1.Value
End Sub

answered May 28, 2015 at 12:54

FreeMan

5,7092 gold badges32 silver badges58 bronze badges

2 Comments

Pat Searle Over a year ago

In that top one i need to cycle through different X values as well as CheckBox values. for example X4=CheckBox1, X5=CheckBox2. I could get the X part to loop but when I tried to add another integer e.g. CheckBoxL, it wouldn't cycle through. It just said CheckBoxL can't be found

FreeMan Over a year ago

Yup, saw that and edited. Use Jeanno's answer for that, and mine for your second issue

Jeanno · Accepted Answer · 2015-05-28 12:57:26Z

0

You can group the checkboxes inside a given frame and try the the following

Sub Test()
    Dim i As Long
    i = 5
    For Each cb In UserForm1.Frame1.Controls
        If Worksheets("Data Directorate").Range("X" & i).Value = True Then cb.Value = True
        i = i + 1
    Next cb
End Sub

answered May 28, 2015 at 12:57

Jeanno

2,8894 gold badges26 silver badges33 bronze badges

1 Comment

vacip Over a year ago

No need for the If statement here, just make the two sides equal. Also, cells(i,26) is somewhat faster than range("X" & i).

Collectives™ on Stack Overflow

Loop repeated code to set userform checkbox values

5 Answers 5

6 Comments

1 Comment

4 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

1 Comment

4 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related