if function using option buttons.

[rodney_malod]

Hi, ive created a userform with two pages, each has 3 criteria for a tank selection. one page is size and the other, type.

i have prices for each combo of tank. and wish to use optionboxes on the userform to get a cell to display a price.

i have this code:

Private Sub CommandButton1_Click()

If optionbox6 = True And optionbox7 = True Then
Range("b30").Select
ActiveCell = "630.8"
Else: End If

If optionbox6 = True Then
If optionbox8 = True Then
Range("b30") = "785.40"
Else: End If
Else: End If
If optionbox6 = True And optionbox9 = True Then
Range("b30") = "873.94"
Else: End If

If optionbox5 = True Then
If optionbox7 = True Then
Range("b30") = "791.83"
Else: End If
Else: End If
If optionbox5 = True Then
If optionbox8 = True Then
Range("b30") = "940.88"
Else: End If
Else: End If
If optionbox5 = True Then
If optionbox9 = True Then
Range("b30") = "1037.67"
Else: End If
Else: End If
If optionbox4 = True Then
If optionbox7 = True Then
Range("b30") = "813.81"
Else: End If
Else: End If
If optionbox4 = True Then
If optionbox8 = True Then
Range("b30") = "967.94"
Else: End If
Else: End If
If optionbox4 = True Then
If optionbox9 = True Then
Range("b30") = "1063.17"
Else: End If
Else: End If
UserForm1.Hide

End Sub



as you can see i have been playing with it to get it to work but have had no luck, help me !! :dunno

[Killian]

Hi there :hi:

This is just a case of understanding the syntax and logic for If...Then...Else
You have 3 options on the first page and 3 on the second
For the first page:
if option1 then, in a separate ("nested" if... then...else), find which 2nd page option
elseif option 2 ...repeat
Also, it's a little more readable if you have a variable to assign the price to and then assign that to the range at the end - you can also check to see if a value is being submittedDim sngPrice As Single

If optionbox6 = True Then
If optionbox7 = True Then
sngPrice = 630.8
ElseIf optionbox8 = True Then
sngPrice = 785.4
ElseIf optionbox9 = True Then
sngPrice = 873.94
End If
ElseIf optionbox5 = True Then
If optionbox7 = True Then
sngPrice = 791.83
ElseIf optionbox8 = True Then
sngPrice = 940.88
ElseIf optionbox9 = True Then
sngPrice = 1037.67
End If
ElseIf optionbox4 = True Then
If optionbox7 = True Then
sngPrice = 813.81
ElseIf optionbox8 = True Then
sngPrice = 967.94
ElseIf optionbox9 = True Then
sngPrice = 1063.17
End If
End If

If sngPrice <> 0 Then
ActiveSheet.Range("B3").Value = sngPrice
UserForm1.Hide
Else
MsgBox "Tank Size/Type not selected"
End If

[Bob Phillips]

Another form, which I think is more readable


Dim sngPrice As Single

Select Case True
Case Optionbox6 And Optionbox7: sngPrice = 630.8
Case Optionbox6 And Optionbox8: sngPrice = 785.4
Case Optionbox6 And Optionbox9: sngPrice = 873.94
Case Optionbox5 And Optionbox7: sngPrice = 791.83
Case Optionbox5 And Optionbox8: sngPrice = 940.88
Case Optionbox5 And Optionbox9: sngPrice = 1037.67
Case Optionbox4 And Optionbox7: sngPrice = 813.81
Case Optionbox4 And Optionbox8: sngPrice = 967.94
Case Optionbox4 And Optionbox9: sngPrice = 1063.17
End Select

If sngPrice <> 0 Then
ActiveSheet.Range("B3").Value = sngPrice
UserForm1.Hide
Else
MsgBox "Tank Size/Type not selected"
End If

[rodney_malod]

i see where your coming from, thanks both of you.

however ive tried both sets of code and it reutnrs the error box msg "tank type/size not selected"

i havent a clue why either :(

[Killian]

Now that you've got an example of the If...Then solution, it's worth pointing out that for this situation, xld's Select Case is a better construct to use

Regarding the problem, if one of the option control groups doesn't have an option selected, the if..then/case tests won't be met and the variable won't be set - it's probably worth selecting one from each page at design time or on the form initialize event as defaults so this can't happen

[rodney_malod]

sorry killian you lost me, ive checked and it appears everything is assigned to the correct box

[Bob Phillips]

sorry killian you lost me, ive checked and it appears everything is assigned to the correct box

Obviously we don't have your data and app, but I tested it from what I could see, adn it worked for me, and I am sure Killian would have done the same. As long as an option button within each batch is selected a value is returned.

[rodney_malod]

oh god im so sorry, i had optionbox instead of option button