How can I simplify the code

[yasawa]

Hello
I have this code working on a form, it's working fine, simply writing the caption in the sheet if the box is checked...
I tried using variables, using loops and many other methods to get the same result without having to repeat the lines over and over but none worked


Private Sub OKButton1_Click()

Worksheets("Path").Activate

emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
Cells(emptyRow, 1).Select


If CheckBox1.Value = True Then
ActiveCell.Value = CheckBox1.Caption
ActiveCell.Offset(1, 0).Select
End If

If CheckBox2.Value = True Then
ActiveCell.Value = CheckBox2.Caption
ActiveCell.Offset(1, 0).Select
End If

If CheckBox3.Value = True Then
ActiveCell.Value = CheckBox3.Caption
ActiveCell.Offset(1, 0).Select
End If

If CheckBox4.Value = True Then
ActiveCell.Value = CheckBox4.Caption
ActiveCell.Offset(1, 0).Select
End If

If CheckBox5.Value = True Then
ActiveCell.Value = CheckBox5.Caption
ActiveCell.Offset(1, 0).Select
End If

Unload Me

End Sub

 

[Rick Rothstein]

I highlighted in blue the part that replaces your repeated If..Then code blocks...

Private Sub OKButton1_Click()
  [B][COLOR=#0000ff]Dim Cntrl As Control[/COLOR][/B]
  Worksheets("Path").Activate

  emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
  Cells(emptyRow, 1).Select
  
[B][COLOR=#0000ff]  For Each Cntrl In Me.Controls
    If TypeName(Cntrl) = "CheckBox" Then
      If Cntrl.Value Then
        ActiveCell.Value = Cntrl.Caption
        ActiveCell.Offset(1).Select
      End If
    End If
  Next[/COLOR][/B]
  
  Unload Me

End Sub

NOTE: As written, the loop processes every CheckBox on the UserForm. If you have some CheckBoxes that need to be ignored, you will have to add code in the loop to look for those names in order to skip them.

 

[yasawa]

Thanks a lot, works perfectly...