Loop through Textboxes

[sharky12345]

I have 5 textboxes on a userform and I want to evaluate if they have text entered.

The values are used as a 'CC' list for an email message, so I also need a semi-colon added after each textbox.

Can someone show me how this could work?

 

[Dryver14]

something like this

cells(Nextrow, 4).value = Me.tbEmpName & ";"

 

[Kyle123]

Like this:

    Dim cc  As String
    Dim ctl As Object
    
    For Each ctl In Me.Controls
        If TypeOf ctl Is MSForms.TextBox Then
            If Len(ctl.Value) > 0 Then cc = cc & ctl.Value & ";"
        End If
    Next ctl
    
    MsgBox cc

 

[Dryver14]

Nice one Kyle, I'm stealing that

..........running off to look at TypeOf

 

[Norie]

Are the 5 textboxes the only textboxes on the userform?

 

[Kyle123]

I hope so, or my code will not have the desired effect

 

[sharky12345]

Thanks for the replies - there are more than 5 Textboxes, but the ones that this applies to are named TextCC1, 2, etc...

 

[Norie]

Try this.

Dim strCCList As String
Dim I As Long

    For I = 1 To 5
        With Me.Controls("TextCC" & I)
            If .Value <> "" Then
                strCCList = strCCList & .Value & ";"
            End If
        End With
    Next I

    If strCCList <> "" Then
        MsgBox strCCList
    End If

 

[sharky12345]

Thanks Norie!

 

[Rick Rothstein]

Just so you have alternatives, here is Kyle's approach modified to find just your 5 TextBoxes using the structured names you gave them...

Dim cc  As String
Dim ctl As Object
    
For Each ctl In Me.Controls
    If TypeOf ctl Is MSForms.TextBox Then
        If ctl.Name Like "TextCC*" Then cc = cc & ctl.Value & ";"
    End If
cNext ctl
    
MsgBox c