Multiple Input Boxes on a Userform ?

[Analyze This]

Good Evening,

I have 20 pieces of information (simple number) that will need to be entered into 200 sheets. The questions are the same for every page and I'd like to link the input numbers to the same cells in each sheet. I've been searching on the board for everything I can think of, but I just can't find it. Do I create a Userform and put 20 text boxes on it? If so, how do I then link the input numbers to the cells?

Thank you so much, figuring this out will save HOURS of work!

 

[SIXTH SENSE]

hi!
I think there is no need of input boxes.
Just type all your info in the first sheet and run the code below.
this assumes that all the data has the same location in each sheet.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CopyToAllSheet()
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> Sht <SPAN style="color:#00007F">As</SPAN> Worksheet
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
Application.Calculation = xlCalculationManual
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Sht <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets
    <SPAN style="color:#00007F">If</SPAN> UCase(Sht.Name) <> UCase("sheet1") <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'the sheet name where you first input your 20 info</SPAN>
        Sht.Range("a1:a20").Value = Worksheets("Sheet1").Range("a1:a20").Value
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> Sht
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
Application.Calculation = xlCalculationAutomatic
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Analyze This]

Yes, everything will have the same target cells - however I'm not sure how you're proposing I enter these 20 data points to get there. In case I was not clear with the first post, I need to have the ability to enter these 20 points of data for each sheet, as it will be different.

The 20 points of data will be entered according to each of the 175 reports that will be on each sheet. I'm envisioning a form or a box that opens and gives me the 20 boxes to key in the data as I'm viewing the report on the sheet, then places the 20 numbers I have just entered into their appropriate target cells.

Does that make any sense?

 

[SIXTH SENSE]

Yes, everything will have the same target cells - however I'm not sure how you're proposing I enter these 20 data points to get there. In case I was not clear with the first post, I need to have the ability to enter these 20 points of data for each sheet, as it will be different.

The 20 points of data will be entered according to each of the 175 reports that will be on each sheet. I'm envisioning a form or a box that opens and gives me the 20 boxes to key in the data as I'm viewing the report on the sheet, then places the 20 numbers I have just entered into their appropriate target cells.

Does that make any sense?

hi!
This is my algorithm in your case.
1.first do the input in A1 to A20.
2. then run the code I provided.
What the code do?
1. It will loop thru each Sheet you have from Sheet1 to SheetN.
2. Assuming that that your input data is in Sheet1, it will scape sheet1 because theres is nothing to do in this sheet.
3. When the sheet name is not Sheet1(your source of data) it will copy all
the data from A1 to A20 of sheet1 to other sheets whose name is not sheet1 and put it in A1 to A20 of that sheet.

I think this is the simplest way to transfer data from one sheet to all other sheet.You got less set up and less code.

 

[Analyze This]

Sixth -

I've been trying to find something on the board to solve my dilema, including your suggestions (thanks!). However I just can't seem to get it straight. Perhaps the first question is this: Is it possible to enter more than one piece of information on a user form?

In this project I'm trying to get the same box to come up, listing the prompts for all 20 pieces of information. I want this to be entered into a box as opposed to into cells because the user needs to refer to an image on the page. All the numbers and calculations are being done far to the right, out of view. If a 3 is entered into BoxA, that 3 should end up in cell BC16 on the ActiveSheet. The form can be the same for each page, linking back to the same cell names on whatever sheet is active when the input is given.

Thanks so much to all on the board who keep giving and giving of their time and knowledge!