0

I am new to coding UserForms in VBA and require a bit of assistance with coding a conditional statement on a certain number of pairs of CheckBoxs and TextBoxs. Clearly a loop will be involved, but I just cant seem to incorporate it. Here is one of the methods I have tried and failed with:

    For i = 1 To 12
    If CheckBox(i).Value = True And TextBox(i) = "" Or CheckBox(i).Value = False And TextBox(i).Value <> "" Then
    MsgBox ("Warning")
End If
Next i

Thank you for any help!!!!!! Regards

Improve this question
2
  • What are you trying to achieve? Commented Mar 20, 2016 at 10:31
  • I trying to achieve a situation where the first 12 pairs of Checkbox and TextBox are fully ticked (in the Checkbox case) and filled (in the textbox case). If this is not the case, then a warning message appears. Commented Mar 20, 2016 at 10:45

1 Answer 1

Reset to default
1

In a UserForm are not CheckBox or TextBox collections. Thats why CheckBox(index) or TextBox(index) will not work.

But there is a Controls collection.

 For i = 1 To 12
  'If CheckBox(i).Value = True And TextBox(i) = "" Or CheckBox(i).Value = False And TextBox(i).Value <> "" Then

  If Me.Controls("CheckBox" & i).Value = True And Me.Controls("TextBox" & i).Value = "" _
    Or Me.Controls("CheckBox" & i).Value = False And Me.Controls("TextBox" & i).Value <> "" Then
   MsgBox ("Warning")
  End If
 Next

Assuming all CheckBoxes have corresponding TextBoxes with same numbers, CeckBox1 - TextBox1, CeckBox2 - TextBox2, ... then:

 Dim oControl As Control
 Dim sTextBoxName As String
 For Each oControl In Me.Controls
  If LCase(TypeName(oControl)) = "checkbox" Then
   sTextBoxName = Replace(oControl.Name, "checkbox", "textbox", , , vbTextCompare)
   If oControl.Value = (Me.Controls(sTextBoxName).Value = "") Then
    MsgBox "Warning " & Me.Controls(sTextBoxName).Name & ", " & oControl.Name
   End If
  End If
 Next
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Just a small hint: If Me.Controls("CheckBox" & i).Value = (Me.Controls("TextBox" & i).Value = "") Then will do the same ;)
Out of interest, how could we extend this all check boxes? So n would not be stated. would we stat with some kind of: For Each ctl In Me.Controls statement ?
When I say n, I mean the n in, For i=1 To n.
See my supplement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.