CODEWITHSUNDEEP
excelvbaloops
Pat Searle
Pat Searle

Reputation: 3

Loop repeated code to set userform checkbox values

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?

Upvotes: 0

Views: 457

Answers (5)

BrakNicku
BrakNicku

Reputation: 5991

My approach in this scenario would be:

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

enter image description here

Upvotes: 1

vacip
vacip

Reputation: 5416

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.

Upvotes: 1

basodre
basodre

Reputation: 5770

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

Upvotes: 2

Jeanno
Jeanno

Reputation: 2859

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

Upvotes: 0

FreeMan
FreeMan

Reputation: 5687

For the second portion of your question:

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

Upvotes: 0

Related Questions