populate a text box with a value that is based on a series of selections from several groups of option buttons

[Wombat18]

Hi

It's been a while since I've attempted to create a user form in excel and I'm struggling with trying to achieve the result that I need. I've searched online without luck so far.

Basically I'm trying to populate a text box with a value that is based on a series of selections from several groups of option buttons.

The option buttons for example are ...

Group 1 = paper size (options are A4 & A3), group 2 = paper weight (options are 80gsm & 200gsm) & group 3 = print colour (options are B&W & colour). Based on the selections I would then like to populate text box with a price for the selected combination.

I currently have a worksheet which contains a table that has all the possible combinations (eg. Column A is a named range = "paper_size" ie. A4 & A3. Column B = "paper_weight" and column C = "print_colour". The final column on this worksheet is the cost ie. For the combination of A4, 80gsm & B&W let's say 0.04.

What I would like to do is populate a text box (eg. txt_value) with this amount so that I can then use it in further calculations.

Any help or pointing me in the direction of a solution is greatly appreciated.

Cheers



ith a table that

 

[sijpie]

I have a simple userform here. I named the option buttons according to this scheme
for the sizes:
optPS_A5, optPS_A4, optPS_A3 etc
for the weights:
optPW_80, optPW_100, etc
for the colour:
optPC_w, optPC_c

the price text box is named: tboxPrice

For the userform I have the following code:
Read the comments to see what you need to do to make it work on your sheet


<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> sPSize <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> sgPWeight <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> sPColor <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> vSelection <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><br><SPAN style="color:#007F00">' adjust the following constants to your set up</SPAN><br><SPAN style="color:#00007F">Const</SPAN> sSHTNAME = "PaperDB"<br><SPAN style="color:#00007F">Const</SPAN> s1stCELL = "A1"<br><SPAN style="color:#00007F">Const</SPAN> lCOLPS = 1    <SPAN style="color:#007F00">'size is in 1st column of DB</SPAN><br><SPAN style="color:#00007F">Const</SPAN> lCOLPW = 2    <SPAN style="color:#007F00">'weight is in 2nd col of DB</SPAN><br><SPAN style="color:#00007F">Const</SPAN> lCOLPC = 3    <SPAN style="color:#007F00">'color is in 3rd col of DB</SPAN><br><SPAN style="color:#00007F">Const</SPAN> lCOLPRICE = 4 <SPAN style="color:#007F00">'Price is in 4th col of DB</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br>    Unload Me<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> optPS_A5_Click()<br>    sPSize = "A5"<br>    GetPrice<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> optPS_A4_Click()<br>    sPSize = "A4"<br>    GetPrice<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> optPS_A3_Click()<br>    sPSize = "A3"<br>    GetPrice<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> optPS_A2_Click()<br>    sPSize = "A2"<br>    GetPrice<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> optPW_80_Click()<br>    sgPWeight = 80<br>    GetPrice<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> optPW_100_Click()<br>    sgPWeight = 100<br>    GetPrice<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> optPW_120_Click()<br>    sgPWeight = 120<br>    GetPrice<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> optPC_w_Click()<br>    sPColor = "no"<br>    GetPrice<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> optPC_c_Click()<br>    sPColor = "yes"<br>    GetPrice<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()<br>    <SPAN style="color:#007F00">'read the paper selection into array</SPAN><br>    vSelection = Sheets(sSHTNAME).Range(s1stCELL).CurrentRegion<br>    <br>    <SPAN style="color:#007F00">' reset the option buttons to most used in each frame</SPAN><br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#007F00">' only run the option button change macro on the last change</SPAN><br>    optPS_A4 = <SPAN style="color:#00007F">True</SPAN><br>    optPW_80 = <SPAN style="color:#00007F">True</SPAN><br>    sPSize = "A4"<br>    sgPWeight = 80<br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#007F00">' set the last option button which will now run the macro and set the price</SPAN><br>    optPC_w = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> GetPrice()<br>    <SPAN style="color:#00007F">Dim</SPAN> lS <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> UB1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#007F00">'vSelection has been loaded in UserForm_Initialize</SPAN><br>    UB1 = <SPAN style="color:#00007F">UBound</SPAN>(vSelection, 1) <SPAN style="color:#007F00">'the number of rows in the paper DB</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> lS = 1 <SPAN style="color:#00007F">To</SPAN> UB1   <SPAN style="color:#007F00">' loop through paper sizes</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> vSelection(lS, lCOLPS) = sPSize And _<br>           vSelection(lS, lCOLPW) = sgPWeight And _<br>           vSelection(lS, lCOLPC) = sPColor <SPAN style="color:#00007F">Then</SPAN><br>           <br>           <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> lS<br>    <br>    <SPAN style="color:#00007F">If</SPAN> lS > UB1 <SPAN style="color:#00007F">Then</SPAN><br>        Me.tboxPrice.Value = "Not found"<br>    <SPAN style="color:#00007F">Else</SPAN><br>        Me.tboxPrice.Value = Format(vSelection(lS, lCOLPRICE), "#0.00")<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>