Large Form Help: Handling large number of text boxes and labels

I could use some advice on getting a large form do to what I want it to.

My goal is to use a single form that users can input data into, and all of the data will be stored in the excel spreadsheet the form is attached to. Here is the basic idea: I have dozens of positions, and for each position, I need to allocate their time across 25 different activities. I am working on building a form where the user can define a list of positions, and then select them from a dropdown, and then put in the activity-specific allocation into text boxes for each of the 25 activities. From here, the user will hit save, and all the data for that position will be sent to excel. Also, the user can select a previously saved position and see how the position was allocated.

Question 1: What is the best way to save data from 25 different text boxes on a form to a spreadsheet in excel. Doing something like Application.range("allocations")(1,1) . value = txt_name.value takes FOREVER to repeat for each and every text box. Is there a better way? I tried looping, but it didn't work. I cant even get the txtboxname.value to work. Do I need to set txtboxname = control or something like that? Can I loop through each control in a frame and then reference the name and use that to place it in the correct location in the spreadsheet?

Please Login or Register  to view this content.