Dynamically populating textboxes on a form

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

For i = 1 to 5

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

Next i


--

Regards,
Tom Ogilvy


"Adamaths" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Adamaths
> ------------------------------------------------------------------------
> Adamaths's Profile:
http://www.excelforum.com/member.php...o&userid=31580
> View this thread: http://www.excelforum.com/showthread...hreadid=512806
>

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

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

--
Regards,
Tom Ogilvy


"Adamaths" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Adamaths
> ------------------------------------------------------------------------
> Adamaths's Profile:
http://www.excelforum.com/member.php...o&userid=31580
> View this thread: http://www.excelforum.com/showthread...hreadid=512806
>

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