One form, multiple sheets - is this possible?

[RichP]

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.

 

[HalfAce]

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]

Dan,
this is great, thank you so much for taking a look and posting back.
Rgds,
R.

 

[HalfAce]

More than welcome Rich.
Glad it helped.