Can anyone crack this VBA formula???

[Ben S]

How would i write the following calculation in VBA. I have a form set up and would like textbox10 to calculate the following.

If ComboBox1 ="Support or Police" and ComBoBox2 ="Business"
Then
=(((ComboBox3.Value/12*4.085%*TextBox4.Value)+(TextBox4.Value))/(ComboBox3.value))
If ComboBox1 ="Support or Police" and ComBoBox2 ="Standard"
Then
=(((ComboBox3.Value/12*4.585%*TextBox4.Value)+(TextBox4.Value))/(ComboBox3.value))
If ComboBox1 ="Support or Police" and ComBoBox2 ="Standard £15K"
Then
=(((ComboBox3.Value/12*4.43%*TextBox4.Value)+(TextBox4.Value))/(ComboBox3.value))

I also need to display to two decimal places and use the £ sign

 

[Corticus]

Hi,

untested but should work (fingers crossed):

Sub Tester()

If Combobox1 = "Support" Or "Police" Then
Select Case Combobox2
Case "Business"
TextBox10 = ((ComboBox3.Value / 12 * .004085 * TextBox4.Value) + (TextBox4.Value)) / (ComboBox3.Value)
Case "Standard"
TextBox10 = ((ComboBox3.Value / 12 * 0.004585 * TextBox4.Value) + (TextBox4.Value)) / (ComboBox3.Value)
Case "Standard £15K"
TextBox10 = ((ComboBox3.Value / 12 * 0.00443 * TextBox4.Value) + (TextBox4.Value)) / (ComboBox3.Value)
End Select
End If

End Sub

HTH,
Corticus
This message was edited by Corticus on 2002-10-30 10:30

 

[Ben S]

Does not produce any figures. Is there anything else i have to do
This is the code i have input. Do i have to place any code on other fields

Sub Tester()

If ComboBox1 = "Support" Or "Police" Then
Select Case ComboBox2
Case "Business"
TextBox10 = ((ComboBox3.Value / 12 * 0.004085 * TextBox4.Value) + (TextBox4.Value)) / (ComboBox3.Value)
Case "Standard"
TextBox10 = ((ComboBox3.Value / 12 * 0.004585 * TextBox4.Value) + (TextBox4.Value)) / (ComboBox3.Value)
Case "Standard £15K"
TextBox10 = ((ComboBox3.Value / 12 * 0.00443 * TextBox4.Value) + (TextBox4.Value)) / (ComboBox3.Value)
End Select
End If

End Sub

 

[stevebausch]

I trust you mean a Userform.

Does that code work? What's the default property of a listbox?? Okay, I just looked, it's Value. Value returns what the ListIndex of BoundColumn contains. Maybe you don't have the proper BoundColumn?


Normally, I would explicitly test .List(row,colum), perhaps Trim()ing the result before testing it against "TestString", and perhaps lCase()ing or uCase()ing both items.

OR, if you hard-code the strings into the listbox, you might just want to test for .listindex (if not a multi-select).

If you are dealing with multi-select, then you need to test the .selected property (boolean).