Combining code using variables

Hi,

I am trying to make my very long code more efficient as it becoming a challenge to maintain. The basic portions of the code are as follows:

A userform pops up to ask the user for sets of words seperated by commas. Here is the relevant userform code:

Please Login or Register  to view this content.

In a regular module, I have code that will split the words from each array:

Please Login or Register  to view this content.

and then look for each word in a range and modify these words if found:

Please Login or Register  to view this content.

The actual userform and code currently contains seven sets of commands (one corresponding to each textbox on the userform. Therefore each time I make one modification, to the code, I have to make sure I modify it seven times and then test it seven times to make sure I coded it correctly each time. The number of textboxes on the userform is going to increase and I need help finding a better way to manage all this code.

I tried using:

Please Login or Register  to view this content.

and same thing for the variant (a) in "myList_1(a)" but I am having no luck coding it correctly.

If anyone has experience with these issues, could you please guide me so I can modify my code accordingly?

Thanks.

abousetta

Hi abousetta,

It sounds like you need to loop through the controls on your userform. You can do this with an index number.
I just created a userform and hung 4 textboxes on it. Then in the immediate window I typed

Please Login or Register  to view this content.

and pressed Enter. It came back as "TextBox2"

This means you can loop through your TextBoxes on the Userform with
For i = 0 to 6
StrText = Userform1.Controls(i).Text
'Do what you need here
Next i

Hope this helps. Excel has indexes built into most all its objects.

Hey,
How many controls do you have on your userform?
Start using the immediate window and debug.print

Please Login or Register  to view this content.

then

Please Login or Register  to view this content.

I bet you will find your TextBoxes are Controls 4 through 10.
Run your loop for TextBoxes only.

What I'm saying is that if Control(3) is a Label or ComboBox and you ask for Text it will complain.

See if that works.

Hi,
I was thinking the TabIndex would change the control index number but I'm not finding that true.

How about trying this:

Loop through all the controls and if the
Left(Controls(i).Name,4) is Text then do your code on it.

You suggested that in your last post and I think that is how I'd do it.

abousetta
do you mean .. to loop the control type?

Please Login or Register  to view this content.

@Pike,

Thanks for showing me

Please Login or Register  to view this content.

TypeName wasn't a tool in my toolbox yet but now I see how useful it can be and have needed it many times in the past.

@abousetta
Pike's code is an example of how to tell if the control is a TextBox. It wasn't intended to pass your text (that is in a textbox) to your main routine, but I think you are very close to getting the right answer.

Since your textboxes all have the same naming convention:

Please Login or Register  to view this content.

Note: you need to convert your 7 string variables to one array of Strings.

You didn't implement the code I posted, you implemented something else. You ned my code above and your string variable should be declared as

Please Login or Register  to view this content.

I don't follow the rest of your code as a lot of it makes no sense to me. For example, posn is not an array so why do you keep referring to posn(i)?

Thank you for your patience. Here is the my original code that I am trying to reduce in size by removing the redundancy. As you can see, its just a series of repeated code for each textbox. What I am trying to do is reduce it to one set of code and a loop to perform the actions for each list.

I have tried the modified code after adding the Public declaration and it now does work fine in the userform. Sorry for not understanding what you meant the first time around.

abousetta

I'd put all the code in the userformmodule, starting with

Please Login or Register  to view this content.

I have no idea why I'm reading you code but I just had an idea.

Why don't you have an array called myList_x.

You then let
myList_x = myList_1 and call the repeated code.
Then let myList_x = myList_2 and call the repeated code.

You will need to change the repeated code to deal with myList_x.

Does that save you some carpal tunnel pain?

Something like this.

Functionally the code is fine, but:
1. You should avoid using public variables where they are not necessary (there is no need for cell or i to be public for example).
2. I suggest you do not use the same name for the variables in the declaration of your procedures as you use for the variables you pass to them, as that may lead you to believe they are intrinsically linked in some way (which they are not, even though they are being passed ByRef)