MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

MS Excel Form problem

  Asked By: Bertha    Date: Sep 17    Category: MS Office    Views: 1111

I have a form using in Excel 2003 that's 7 columns and 20 rows I need to
populate the Textboxes with values from a worksheet. I want to use a loop
statment that fills the first row, then increments to the next row until all 20
are filled. I have named each row the same name except the last character is the
row number 1-20.

ie on the form
NameRow1 AddressRow1 CityRow1 StateRow1 ZipRow1
NameRow2 AddressRow2 CityRow2 StateRow2 ZipRow2

This is what I want to happen?

Sub test()
Dim RowNumber As Integer
Dim FormRow As Integer
Dim NameRow As Object
Dim AddressRow As Object
Dim CityRow As Object
Dim StateRow As Object
Dim ZipRow As Object

RowNumber = 3 'Row in Data sheet
FormRow = 1 'Row on form
NameRowString = "NameRow" 'first part of the named object
Do While FormRow < 21
NameRowVar = NameRowString & FormRow
'doesn't work
Set NameRow.Name = NameRowVar
'this works if I use NameRow1
'but NameRow needs to be variable to be use in all 20 rows
NameRow = Cells(RowNumber, 4).Value
AddressRow & FormRow.Value = Cells (RowNumber,5).Value
CityRow & FormRow.Value = Cells (RowNumber,6).Value
StateRow & FormRow.Value = Cells (RowNumber,7).Value
ZipRow & FormRow.Value = Cells (RowNumber,8).Value
'increment Rows
RowNumber = RowNumber + 1
FormRow = FormRow + 1
End Sub



1 Answer Found

Answer #1    Answered By: Harold Graham     Answered On: Sep 17

Your code works down to the point you have indicated.

Then you would need to make the next line something like

Set NameRow = MyForm.Controls(NameRowVar)

If you need to post back for more help it would be good if you can tell us
what error you are getting and on what line.

Didn't find what you were looking for? Find more on MS Excel Form problem Or get search suggestion and latest updates.