CODEWITHSUNDEEP
excelvbauserform
user6089076
user6089076

Reputation:

Conditional statements on CheckBox & TextBox in Userforms (VBA)

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

Upvotes: 0

Views: 3441

Answers (1)

Axel Richter
Axel Richter

Reputation: 61852

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

Upvotes: 1

Related Questions