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.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.