Iterate through Userforms and Textboxes within Userforms

[saint_lundie]

Hi,
I am using a number of userforms containing textboxes to gather data from users. Within a module i would then like to iterate through each textbox in each userform and assign the value of the textbox to a variable:

I have tried a few different things but with no success. Below is the code that i know is incorrect buy gives an example of what i am trying to do

For n = 1 To UserForm1.NumRun.Value ' NumRun is another textbox that gives the total number of userforms
    For i = 1 To 8
        Ref = UserForm & n.TextBox & i.Value
        ' do something with Ref
    next i
next n

Thanks in advance for any help

 

[Norie]

Wouldn't it be easier to use one userform, perhaps with a multipage control?

It would then be straightforward to loop through the controls.

For Each pg In UserForm1.MultiPage.Pages
    For Each ctl In pg.Controls 
        Ref = ctl. Value 
         ' do stuff with Ref
    Next ctl
Next pg

 

[saint_lundie]

Hi Norie.
Thanks for the reply. Unfortunately it's not that easy. There are other textboxes on the userform so a simple iteration through all the textboxes wouldn't work. As for the multipage idea, i dont think i can do it this was as i have it set up to only show the number of userforms required (up to 12 but could be only be 1). As each new form is displayed a macro is initiated to generate a new sheet (from a template) and populate with specified text box values.

 

[Norie]

I don't see why you can't use a multipage.

Pages on a multipage can be added/deleted/hidden/shown as required.

Also, what I posted is for iterating through all controls, which is kind of what you asked about : ), but it should be easy to adapt.

What exactly do you gave on the userforms?

Are they all completely different?

 

[saint_lundie]

Hi Norie,
You're right i can use multipage. I just hadn't used it before. I've had a play and it seems to work but i'm still unsure as to how to iterate through only some controls (again you're right i was a bit to ambiguous). The controls i want to iterate through all have a similar name, ie "Textbox1", "textbox2" There are other textboxes on the userform also.
Any ideas?
Thanks

 

[Norie]

If you have some sort of naming convention then you could use a loop and the Controls collection.

Here's a very simple example.

For I = 1 To 2
    MsgBox Me.Controls("TextBox" & I).Value
Next I