Looping through text box controls on User Form

magic_orange

New Member
Joined
Jun 23, 2008
Messages
8
Hi all, I'm a newbie to this board so please if you could, be gentle with me...

What I'm currently flummoxed with is how to loop through the text box controls on a user form one by one, find their particular names and then change the caption value on each one via another Excel workbook which I'm reading by ADO. The last bit is FYI but not pertinent - I simply want to know what the code is to loop through the controls on a form and when I've found the one(s) I want to set a value on them. Can anyone help? I'd make you a virtual cup of tea....

Cheers
Scott
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
For example:
Code:
    Dim ctl As msforms.Control
    For Each ctl In Me.Controls
        If TypeOf ctl Is msforms.TextBox Then
            ctl.Text = ctl.Name
        End If
    Next ctl
 
Upvote 0
Thanks Rory. What I actually want to do though is manipulate that text box if it matches a value in an ADO recordset loop (I have a dataset with matching control names to the form). When I find it I want to alter the value within that text box according to another value within the recordset. In shorthand partial pseudo code terms :-

dim txtbox as text box

do until rst.eof

for each ctl in me.controls
if ctl.name=rst.fields("ControlName")
set txtbox=ctl
exit for
end if
next
txtbox.caption=rst.fields("Value")
rst.movenext
loop
 
Upvote 0
I would probably just do something like this:
Code:
dim txtbox as MSForms.Textbox
on error resume next
do until rst.eof
   set ctl = Me.Controls(rst("ControlName"))
   if not ctl is nothing then
       ctl.text=rst("Value")
       set ctl = nothing
   End If
   rst.movenext
loop
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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