Looping through controls on userform

J

Jonnyoforem

New Member
Joined
May 22, 2015
Messages
32
The following code is designed to find values on a worksheet and put those values into text boxes on a user form that will be opened at the end of the macro. The following code is an example of what I want and it works great.

Code: 
Dim cCont As Control
Dim i As Integer

iRow = 30

i = 1
    For Each cCont In updatePackerForm.Controls
        If TypeName(cCont) = "TextBox" Then
            cCont.Value = Worksheets("TrainingMatrix") _
            .Range("Packer")(iRow, i).Value
        End If
        
        If TypeName(cCont) = "TextBox" Then
            i = i + 1
        End If
        
     Next cCont
    
        updatePackerForm.Show

It opens the userform, updatePackerForm, and the values I want are populated. However when I run the code like the following, it doesn't work

Code: 
Dim cCont As Control
Dim i As Integer
Dim k As Variant

iRow = 30

i = 1
k = Cells(1, 6).Value 'This makes k =                                                 '  "updatePackerForm"


    For Each cCont In                                      VBA.UserForms.Add(k).Controls
        If TypeName(cCont) = "TextBox" Then
            cCont.Value = Worksheets("TrainingMatrix") _
            .Range("Packer")(iRow, i).Value
        End If
        
        If TypeName(cCont) = "TextBox" Then
            i = i + 1
        End If
        
     Next cCont
    
        updatePackerForm.Show

The code runs with no errors, but when it shows the userform, none of the values are in the textboxes. The textboxes are completely blank. I tried to Dim k As UserForm but then I get an error on the lilne
Code: 
k = Cells(1, 6).Value

Any ideas? Thanks in advance for the help
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
For each userform:

Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
    [COLOR=darkblue]Call[/COLOR] LoadTextBoxes(Me)
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


In a regular module:

Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] test()

    [COLOR=darkblue]Dim[/COLOR] k [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    
    k = Cells(1, 6).Value
    
    VBA.UserForms.Add(k).Show
        
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Sub[/COLOR] LoadTextBoxes(uf [COLOR=darkblue]As[/COLOR] UserForm)

    [COLOR=darkblue]Dim[/COLOR] cCont [COLOR=darkblue]As[/COLOR] Control
    [COLOR=darkblue]Dim[/COLOR] iRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    
    iRow = 30
    
    i = 1
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] cCont [COLOR=darkblue]In[/COLOR] uf.Controls
        [COLOR=darkblue]If[/COLOR] TypeName(cCont) = "TextBox" [COLOR=darkblue]Then[/COLOR]
            cCont.Value = Worksheets("TrainingMatrix") _
                .Range("Packer")(iRow, i).Value
            i = i + 1
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
     [COLOR=darkblue]Next[/COLOR] cCont
        
[COLOR=darkblue]End[/COLOR] Sub

Hope this helps!
 
Upvote 0
Hey Domenic thanks so much for the help! Unfortunately I'm a little confused.
Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
    [COLOR=darkblue]Call[/COLOR] LoadTextBoxes(Me)
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
Is the above piece of code something I add to every single user form? If that's the case I can't do that because those user forms are used elsewhere as well, and I can't have them always calling the procedure LoadTextBoxes (sorry I should've been more specific at the start, this project is quite large though and there's a lot going on, it's hard to explain everything it's doing)
 
Upvote 0
Going back to what I had originally. When I Dim k As UserForm instead of variant, I get the following error: Compile error: Invalid use of property. When the error triggers it highlights this part of my code

Code: 
k = Cells(1, 6).Value

Is there a different way to tell excel that I want k to equal that value if I dim k As UserForm?
 
Upvote 0
Jonnyoforem said:
Is the above piece of code something I add to every single user form? If that's the case I can't do that because those user forms are used elsewhere as well, and I can't have them always calling the procedure LoadTextBoxes (sorry I should've been more specific at the start, this project is quite large though and there's a lot going on, it's hard to explain everything it's doing)
Click to expand...

In that case, we use a public boolean variable that flags whether the textboxes should be load. Also, the variable 'k' should be declared as a string. Therefore, try the following...

For each userform:

Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
    [COLOR=darkblue]If[/COLOR] bLoadTextBoxes [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]Call[/COLOR] LoadTextBoxes(Me)
        bLoadTextBoxes = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

In a regular module:

Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Public[/COLOR] bLoadTextBoxes [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR]

[COLOR=darkblue]Sub[/COLOR] test()

    [COLOR=darkblue]Dim[/COLOR] k [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    k = Cells(1, 6).Value
    
    bLoadTextBoxes = [COLOR=darkblue]True[/COLOR]
    
    VBA.UserForms.Add(k).Show
        
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Sub[/COLOR] LoadTextBoxes(uf [COLOR=darkblue]As[/COLOR] UserForm)

    [COLOR=darkblue]Dim[/COLOR] cCont [COLOR=darkblue]As[/COLOR] Control
    [COLOR=darkblue]Dim[/COLOR] iRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    
    iRow = 30
    
    i = 1
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] cCont [COLOR=darkblue]In[/COLOR] uf.Controls
        [COLOR=darkblue]If[/COLOR] TypeName(cCont) = "TextBox" [COLOR=darkblue]Then[/COLOR]
            cCont.Value = Worksheets("TrainingMatrix") _
                .Range("Packer")(iRow, i).Value
            i = i + 1
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
     [COLOR=darkblue]Next[/COLOR] cCont
        
[COLOR=darkblue]End[/COLOR] Sub

If you want to assign the userform to a variable, you can do as follows...

Code: 
[COLOR=darkblue]Dim[/COLOR] uf [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
[COLOR=darkblue]Dim[/COLOR] k [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]

k = Cells(1, 6).Value

[COLOR=darkblue]Set[/COLOR] uf = VBA.UserForms.Add(k)

uf.Show

Hope this helps!
 
Upvote 0
You my friend are a genius! Thank you thank you so so much! This worked like a charm.

Just out of curiosity, if you have the time, what was wrong with my original code? It worked fine when I specified that name of the user form, but when I stored the name in a variable and called it, nothing happened.
 
Upvote 0
That's because initially this part of the code...

Code: 
VBA.UserForms.Add(k).Controls

...creates an instance of the userform and adds it to the UserForms collection. And so this instance of the userform gets its textboxes loaded with values. Then, when you show the userform using this line of code...

Code: 
updatePackerForm.Show

...a separate instance of the userform is created, added to the UserForms collection, and shown. But it's the first instance of the userform that had the textboxes loaded with values, not the second one. Hence, the textboxes in the second one are blank. You can see this for yourself by showing the first instance of the userform, replacing...

Code: 
updatePackerForm.Show

with

Code: 
VBA.UserForms(0).Show

Note that the index for the UserForms collection is 0-based.
 
Upvote 0
Hey Domenic,

You mentioned that this part of the code...
Code: 
VBA.Userforms.Add(k).Show
Creates an instance of the userform. When is that instance removed from excel's memory? I'm having that particular line of code cause problems in a completely separate macro. I've created a new thread about it here http://www.mrexcel.com/forum/excel-...g-userform-interferes-separate-procedure.html.

If any clarification is needed just let me know. Thanks in advance
 
Upvote 0
I see that Rory is helping you in that thread. He's very knowledgeable, so I'll leave you in his excellent hands.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Userform textbox populated with listbox data how to edit
Replies
6
Views
479
pure vito
P
H
  • Question
VBA Project, Copy enabled labels and textboxes from a userform to another userform
Replies
0
Views
187
HosEsf
H
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
315
pure vito
P
P
  • Question
Userform Listbox Edit Rows Within Sheet
Replies
1
Views
424
pure vito
P
S
  • Solved
User Form Textbox looping
2
Replies
12
Views
557
sjtjr73
S

Forum statistics

Threads
1,214,653
Messages
6,120,750
Members
448,989
Latest member
mariah3

Share this page

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
Back
Top