One form, multiple sheets - is this possible?

RichP

Board Regular
Joined
Jul 31, 2002
Messages
63
Hi there,
Can anyone please let me know if this is possible, and if it is tell me how? I would like to be able to launch the same form from several pages, and use it to fill data to cells on the page from which the form was launched. I'll explain: My workbook contains 3 sheets, each one dedicated to configuring and pricing a particular product. The last line for each product is for "Ancillary Services" and to price this I have a form with a number of check boxes on it - for each of the services; selecting a box (or not) enters a 1 or a 0 into a calculation on the page. To do this, the check boxes are linked to cells on the particular sheet. Not being an expert in these things, I have one (identical) form per sheet. Is there any way that I can use a single form, but link the check boxes to the page from which the form was launched, and not have any interdependencies between the sheets?

Hope I've explained this properly... and thanks in advance!
R.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Sure Rich, check this out.
Get rid of:
The two userforms for sheets 2 & 3. (We are talking about userforms. right?)
and the ControlSource properties for all 3 of the checkboxes on the one remaining userform.
In the VB editor, show your userform, right click on it and choose View code. Replace the two lines that show up in the code module (white area on the right) by copying & pasting this in.


<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox1_Click()
<SPAN style="color:#00007F">If</SPAN> CheckBox1 <SPAN style="color:#00007F">Then</SPAN>
ActiveSheet.[A1] = 1
Else: ActiveSheet.[A1] = 0
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox2_Click()
<SPAN style="color:#00007F">If</SPAN> CheckBox2 <SPAN style="color:#00007F">Then</SPAN>
ActiveSheet.[A2] = 1
Else: ActiveSheet.[A2] = 0
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox3_Click()
<SPAN style="color:#00007F">If</SPAN> CheckBox3 <SPAN style="color:#00007F">Then</SPAN>
ActiveSheet.[A3] = 1
Else: ActiveSheet.[A3] = 0
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Now you can press AltQ to close the VB editor and get rid of any buttons you have on sheets 2 & 3 that used to call up their own userforms and simply copy the button from sheet1 and paste it on the other two sheets.
(If you kept the right button for the userform that remains, you should be good to go.)

Hope it helps,
Dan
 

RichP

Board Regular
Joined
Jul 31, 2002
Messages
63
Dan,
this is great, thank you so much for taking a look and posting back.
Rgds,
R.
 

Forum statistics

Threads
1,147,510
Messages
5,741,577
Members
423,668
Latest member
Audorin

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