Dynamically populating textboxes on a form

[Adamaths]

Hi,

Is there anyway I can auto populate a set of similarly named textboxes
on a user form.

For example, if I have textboxes named txtReporter1 to txtReporter5 I
want to run some code along the lines of:

For i = 1 to 5

FormName.txtReporter & i.Caption = String Value

Next i

The textboxes already exist on the form and are blank but need to
populated differently depending on how a user has accessed the form.

As always, any help is greatly appreciated.

Kind Regards,

Adam

 

[Tom Ogilvy]

For i = 1 to 5

FormName.Controls("txtReporter" & i).Value = String Value

Next i

 

[Adamaths]

Hi,

Thanks for your help on this. I'm still having what is hopefully a
minor problem. The reason for this, I believe is that I'm populating
the form dynamically in a sub routine to which I am passing the name of
the form as a string since the form to populate can change each time.

At the moment, my code reads:

Sub PopulateHeadingsByName(FormName As String)

Dim oUserForm As Object 'Will hold the name of the form to
populate

'Set the object to be the user form we wish to populate
Set oUserForm = UserForms.Add(FormName)

'Cycle through each part of the form - NumberOfComponents is a
public variable
For i = 1 to NumberofComponents

'Populate the txtPeriod textbox
oUserForm.Controls("txtHeadingPart" & i).Value =
RequiredValue

Next i

The code compiles and runs without any error/debug messages. I know
that the required value is not null and that the control exists on the
form, yet it is not populated. I'm guessing this is due to the way I
am definining the form as an object?

Any help to get this working would be greatly apprecaiated.

Regards,

Adam

End Sub

 

[Tom Ogilvy]

I suspect it is populated. As a debug approach, show the form after you
populate it.


Sub PopulateHeadingsByName(FormName As String)

Dim oUserForm As Object 'Will hold the name of the form to
populate

'Set the object to be the user form we wish to populate
Set oUserForm = UserForms.Add(FormName)

'Cycle through each part of the form - NumberOfComponents is a
public variable
For i = 1 to NumberofComponents

'Populate the txtPeriod textbox
oUserForm.Controls("txtHeadingPart" & i).Value = RequiredValue

Next i
oUserForm.Show

End Sub


I suspect that you are probably creating a new instance of the form and not
the instance you intend to work with.

I would pass in a useform object rather than a text string. Or if the
userform already exits (it is already loaded), then retrieve it from the
userforms collection rather than adding it.


See method 3 in
http://support.microsoft.com/kb/207714/en-us

 

[Adamaths]

Many thanks for your help - it is now working a treat.

In the end I added this into the main code of the previous forms
button:

For i = 0 To UserForms.Count - 1
If UserForms.Item(i).Name = FormName Then
PopulateHeadingsByName (i)
End If
Next i

In the Open Workbook event I have loaded all forms then hidden all but
one, so this finds the index of the form I want to show and passes that
to my other subroutine.

By then using

UserForms.Item(Index).Controls(textboxname).value = reuiqred value

they all get popoulated and shown.

Works brilliantly.

Thanks for all your help.

Regards,

Adam