Forms and sheetranges

[Jos Vens]

Hi,


can anyone give me suggestions to solve this issue:

I have a sheet of 40 rows and 5 columns. I don't want the user enter the
sheet and will use a form instead. I want the easiest way to let the user
enter the data in the form and then export it again to the sheet.

I can eg. make a form with 200 textboxes with a special name (txt01_01 upto
txt40_05), but how can I reference them in a procedure like this:

for vRow = 1 to 40
for vCol = 1 to 5
cells(vRow,vCol)= txt & format(vRow,"00") & "_" & format (vCol,"00")
next
next

also, the opposite to fill the form must work

for vRow = 1 to 40
for vCol = 1 to 5
txt & format(vRow,"00") & "_" & format (vCol,"00") =
cells(vRow,vCol)
next
next

Is this a good way of working of are there better suggestions to do the job?
I also don't know how I must reference the objects of the form in a for-next
loop?

Thanks
Jos Vens

 

[Jim Rech]

You'd access the text boxes like this:

UserForm1.Controls("txt" & Format(vRow, "00") & "_" & Format(vCol,
"00")).Text

--
Jim Rech
Excel MVP
| Hi,
|
|
| can anyone give me suggestions to solve this issue:
|
| I have a sheet of 40 rows and 5 columns. I don't want the user enter the
| sheet and will use a form instead. I want the easiest way to let the user
| enter the data in the form and then export it again to the sheet.
|
| I can eg. make a form with 200 textboxes with a special name (txt01_01
upto
| txt40_05), but how can I reference them in a procedure like this:
|
| for vRow = 1 to 40
| for vCol = 1 to 5
| cells(vRow,vCol)= txt & format(vRow,"00") & "_" & format
(vCol,"00")
| next
| next
|
| also, the opposite to fill the form must work
|
| for vRow = 1 to 40
| for vCol = 1 to 5
| txt & format(vRow,"00") & "_" & format (vCol,"00") =
| cells(vRow,vCol)
| next
| next
|
| Is this a good way of working of are there better suggestions to do the
job?
| I also don't know how I must reference the objects of the form in a
for-next
| loop?
|
| Thanks
| Jos Vens
|
|
|

 

[Jos Vens]

Thanks Jim,

this solves my problem. I also was wondering if it's the only solution to
solve my problem?

Jos