Capping result from vba calculation

[jose001]

Afternoon Excel Gurus

I have a very perplexing problem which I can't get my head around. Would really appreciate some advice on the best solution.

On a Userform I have 1 ComboBox, 2 TextBox's and a button. The user makes a selection from the ComboBox and enters a value in the first Textbox. Excel then multiplies the first Textbox by a percentage and fills the third box. Everything is working OK (see below) but I need to ‘cap’ the result depending on the value of the combo i.e if the ComboBox is “0-1” the result from the calculation cannot exceed 25,000. Does anyone have any ideas on how I can do this?

Private Sub ComboBox1_Change()

If ComboBox1.Value = "0-1" Then
TextBox2.Value = TextBox1.Value * 0.01
ElseIf ComboBox1.Value = "1-5" Then
TextBox2.Value = TextBox17.Value * 0.005
ElseIf ComboBox1.Value = "5+" Then
TextBox27.Value = TextBox17.Value * 0.003
End If

End Sub

 

[rafaelspeixoto]

Did you try to use another if?

Private Sub ComboBox1_Change()

If ComboBox1.Value = "0-1" Then
CalculatedVal = TextBox1.Value * 0.01
if CalculatedVal <=25000 then
  TextBox2.Value = CalculatedVal 
else
  TextBox2.Value = 25000
end if
ElseIf ComboBox1.Value = "1-5" Then
TextBox2.Value = TextBox17.Value * 0.005
ElseIf ComboBox1.Value = "5+" Then
TextBox27.Value = TextBox17.Value * 0.003
End If

End Sub

 

[jose001]

Many Thanks for the quick reply Rafa, I didn't even realise you could do that. I'll try it now and let you know.

Thanks again

 

[jose001]

Hi Rafa,

Is this terribly wrong? It's not working at the moment

Private Sub ComboBox1_Change()

If ComboBox1.Value = "0-1" Then
calculatedVal = TextBox1.Value * 0.01
If calculatedVal <= 25000 Then
  TextBox2.Value = calculatedVal
Else
  TextBox2.Value = 25000
End If

ElseIf ComboBox1.Value = "1-5" Then
calculatedVal = TextBox1.Value * 0.005
ElseIf calculatedVal <= 62500 Then
  TextBox2.Value = calculatedVal
Else
  TextBox2.Value = 62500
End If

ElseIf ComboBox1.Value = "5+" Then
calculatedVal = TextBox1.Value * 0.003
ElseIf calculatedVal <= 75000 Then
  TextBox2.Value = calculatedVal
Else
  TextBox2.Value = 75000
End If


End Sub

 

[rafaelspeixoto]

All looks fine to me!