VBA Option Buttons

[bergsmat]

I have an equation that I would like to be able to have spit out 3 different outputs depending on which opion button is highlighted (Standard, Conservative, Aggresive). I cannot seem to get the equation to work for Conservative or Aggressive. Any help would be great.Tyler

Private Sub CommandButton1_Click()    
'This is the calculate button
        Dim lRow As Long  
        Dim ws As Worksheet    
       Set ws = Sheet5        

       If IsNumeric(TextBox3.Value) And IsNumeric(TextBox1.Value) And IsNumeric(TextBox2.Value) Then   
 'Calculates tool cost based on input width and pich    
       Cost = (Sheet7.Range("J18") * TextBox3.Value) + (Sheet7.Range("J19") * TextBox1.Value) + (Sheet7.Range("J20") * TextBox2.Value) + Sheet7.Range("J17")    
       End If        

       If ActiveSheet.Shapes("OptionButton1").ControlFormat.Value = True Then 
'Returns cost to worksheet if standard is highlighted
       Range("L16").Value = Cost        

'Returns cost to worksheet if Conservative is highlighted 
       Else If ActiveSheet.Shapes("OptionButton2").ControlFormat.Value = True Then
        Range("L16").Value = Cost * 1.1  
     
'Returns cost to worksheet if Aggresive is highlighted    
       Else If ActiveSheet.Shapes("OptionButton3").ControlFormat.Value = 1 Then    
       Range("L16").Value = Cost * 0.9 
       End If
End Sub

 

[Andrew Poulsom]

In what way isn't it working? A TextBox contains Text so to do arithmetic with it you need to use the Val function eg Val(TextBox3.Value).

 

[bergsmat]

I changed my code to this the following but I still get a "Must be first statement on the line" compile error with the two Else If statements showing up red.

Private Sub CommandButton1_Click()
    'This is the calculate button
    
    Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Sheet5
    
    If IsNumeric(TextBox3.Value) And IsNumeric(TextBox1.Value) And IsNumeric(TextBox2.Value) Then
    'Calculates tool cost based on input width and pich
    Cost = (Sheet7.Range("J18") * Val(TextBox3.Value)) + (Sheet7.Range("J19") * Val(TextBox1.Value)) + (Sheet7.Range("J20") * Val(TextBox2.Value)) + Sheet7.Range("J17")
    End If
    
    If OptionButton1 = True & OptionButton2 = False & OptionButton3 = False Then
    
    'Returns cost to worksheet if standard is highlighted
    Range("L16").Value = Cost
    
    'Returns cost to worksheet if Conservative is highlighted
    Else If OptionButton1 = False & OptionButton2 = True & OptionButton3 = False Then
    Range("L16").Value = Cost * 1.1
        
    'Returns cost to worksheet if Aggresive is highlighted
    Else If OptionButton1 = False & OptionButton2 = False & OptionButton3 = True Then
    Range("L16").Value = Cost * 0.9
    
    
    End If
End Sub

 

[Andrew Poulsom]

You need ElseIf rather than Else If. Also you need And instead of & here:

If OptionButton1 = True & OptionButton2 = False & OptionButton3 = False Then

although if they in the same Group only one can be True.

 

[bergsmat]

Thanks, I got it now!