Multiple Input Boxes on a Userform ?

Analyze This

Board Regular
Joined
Jul 25, 2003
Messages
133
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!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
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

Board Regular
Joined
Jul 25, 2003
Messages
133
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

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
Analyze This said:
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

Board Regular
Joined
Jul 25, 2003
Messages
133
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,096
Messages
5,768,058
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top