Issue with array created from value passed from function

I am trying to write code that will loop through controls in a frame on a userform, find text boxes and if the text box is empty then use a function to add a specified value to an array. I then want to display a message box based on the array showing which text boxes are empty.

I have the follow Sub and Function.

Private Sub CommandButton1_Click()
Dim ctrl1 As Control
Dim nTB As Integer
Dim asTB() As String
Dim sTB As String
Dim i As Integer

nTB = 0

    For Each ctrl1 In NewReferral.Frame10.Controls
        If TypeName(ctrl1) = "TextBox" Then

                If ctrl1.Value = "" Then
                    'sTB = WhichTB(ctrl1)
                    nTB = nTB + 1
                    ReDim Preserve asTB(1 To nTB)
                    asTB(nTB) = WhichTB(ctrl1)
                Else
                End If
            
        End If
    Next ctrl1

For i = LBound(asTB) To UBound(asTB)
    msg = msg & asTB(i) & vbNewLine
Next i
MsgBox "the values of my dynamic array are: " & vbNewLine & msg


Unload Me
End Sub

Function WhichTB(TargetTB As Control)
    
    If TargetTB = Me.TextBox1 Then
        WhichTB = "Sample Text 1"
    Else
    End If
    
    If TargetTB = Me.TextBox2 Then
        WhichTB = "Sample Text 2"
    Else
    End If
    
    If TargetTB = Me.TextBox3 Then
        WhichTB = "Sample Text 3"
    Else
    End If
    
End Function

My problem is that the message box displays the same text for each empty text box. For example, say I leave all 3 text boxes empty. My message box will display:

"the values of my dynamic array are:

Sample Text 3
Sample Text 3
Sample Text 3"

when what I was really looking for is:

"the values of my dynamic array are:

Sample Text 1
Sample Text 2
Sample Text 3"

I am pretty sure that the problem is that the "If" statements in the function reset the value for "WhichTB" on each subsequent encounter of an empty text box. I don't know how to pass discrete values for "WhichTB" back to the Sub and then to the array.

I hope that makes sense.

I appreciate any help.

Hi

Can you attach an example workbook with all the necessary functionality. Much easier for us to work with something rather than having to try to build it all.

rylo

Here is the attached workbook.

The code in question is under the UserForm "NewReferral".

Hi

In your example file, change the function code to be

Function WhichTB(TargetTB As Control)
Dim WhichTB1 As String
    
    If TargetTB.Name = Me.TextBox1.Name Then
        WhichTB = "Date"
    Else
    End If
    
    If TargetTB.Name = Me.TextBox2.Name Then
        WhichTB = "Trust Name/Case Name"
    Else
    End If
    
    If TargetTB.Name = Me.TextBox3.Name Then
        WhichTB = "Referred By"
    Else
    End If
    
End Function

I just opened the form, left everything blank, and pressed the OK button and it brought back 3 lines
Date
Trust Name/Case Name
Referred By

Is that correct?

rylo

rylo,

That worked. I don't know that I fully understand why though. I get why we needed to add the .Name tags to everything. What I don't get is how declaring a variable (WhichTB1) that we don't use anywhere else in the function or back in the main sub fixed the problem.

Can you provide any insight?

Hi

All I did was add the .name tags. WhichTB1 is already in the function in the workbook attached to #3 - I didn't put it there, and it isn't used. It really was only adding the .name that fixed things.

rylo

You're right. The

Dim WhichTB1 as String

was left over from me trying ever conceivable thing I could to get the code to run correctly. The workbook I added was not my actual workbook (code cleaned up and stuff removed for privacy). I was comparing your code to my actual workbook (where I had remove the WhichTB1 code because it didn't do anything).
I mistakenly thought you added it and couldn't for the life of me figure out why.

This code makes so much more sense to me now.

Thank you.