Looping thru userform textboxes

[njimack]

I need to test the value of several userform textboxes and I'm currently using the code

For Each Ctrl In .Controls

where Ctrl is declared as a Control.

So far, so good. However, I've noticed that the order in which the controls are tested doesn't correspond with my tab order (suspect it may be the order they were created).

Other than re-creating my userform in the order I wish the controls to be tested, is there a method for specifying the order they should be tested?

Many thanks

 

[almagg]

how about the fact that each textbox has a number.
i have used that idea for:

strSeason = UserForm4.TextBox2

you can run a loop thru TextBox(i).

just a thought

 

[njimack]

Thanks but how would I do that if I've renamed the textboxes, e.g. txt_Firstname, txt_Surname, txt_Position?

 

[Norie]

Neil

Have you considered validating the textbox individually?

Perhaps using their Exit or Change event?

 

[njimack]

Have you considered validating the textbox individually?

Perhaps using their Exit or Change event?

Norie,

I'm using a Before_Update event on some of my textboxes, but I have 27 identical textboxes into which the user will either enter a positive value, a zero, or the text "Unlimited"

In the interests of efficient code, I was hoping to test these when the user presses the command button which transfers the data to the worksheet.

The code does what it's supposed to, apart from the order in which it tests the textboxes. This may confuse the user if I alert them to an error in the 27th textbox when there are also errors in earlier ones.

So I suppose I'll have to choose inefficiency to guarantee user-friendliness!

 

[Norie]

Neil

You mentioned in a previous post that the textboxes had names like FirstName etc.

What are the names of the 27 textboxes that will have a positive value, zero or unlimited?

 

[njimack]

Norie,

Here's the structure of the form at the moment, although I'm open to suggestions if it could be improved on. The following are all Textboxes...

• txt_Firstname
  txt_Surname
  txt_Position
  txt_Level
  TextBox1
  TextBox2
  .
  .
  TextBox27

The first 3 boxes only accept text, the fourth only accepts "A", "B", or "C". All the others accept positive values, zero or "Unlimited"

I've named them this way as I have specific variables for the first 4 boxes. I wanted to leave the remaining boxes named as they are to make it easier to transfer their values to the worksheet, using something like:

.Cells (n+1,2) = Controls("Textbox" & n).Text

but as I said, I'm open to suggestions.

So far, the only thing I'm having trouble with is looping through the textboxes in their Tab order (or based on their name), rather than the order they were created.

 

[Norie]

Neil

I really don't see what the problem is.

You've got a pretty forward naming convention there so why not use it?