Looping thru userform textboxes

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
I need to test the value of several userform textboxes and I'm currently using the code
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

almagg

Well-known Member
Joined
Aug 21, 2002
Messages
705
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Thanks but how would I do that if I've renamed the textboxes, e.g. txt_Firstname, txt_Surname, txt_Position?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,328
Office Version
  1. 365
Platform
  1. Windows
Neil

Have you considered validating the textbox individually?

Perhaps using their Exit or Change event?
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,328
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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:
Code:
.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

Well-known Member
Joined
Apr 28, 2004
Messages
76,328
Office Version
  1. 365
Platform
  1. Windows
Neil

I really don't see what the problem is.

You've got a pretty forward naming convention there so why not use it?
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,561
Messages
5,765,109
Members
425,260
Latest member
worldbfreebase

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top