Forms and sheetranges
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 |
Forms and sheetranges
You'd access the text boxes like this:
UserForm1.Controls("txt" & Format(vRow, "00") & "_" & Format(vCol, "00")).Text -- Jim Rech Excel MVP "Jos Vens" wrote in message ... | 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 | | | |
Forms and sheetranges
Thanks Jim,
this solves my problem. I also was wondering if it's the only solution to solve my problem? Jos "Jim Rech" schreef in bericht ... You'd access the text boxes like this: UserForm1.Controls("txt" & Format(vRow, "00") & "_" & Format(vCol, "00")).Text -- Jim Rech Excel MVP "Jos Vens" wrote in message ... | 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 | | | |
All times are GMT +1. The time now is 01:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com