ExcelBanter - Formulae In User Forms

Hi Graham,

Let's assume you put your table of fees in the range A1:B5 on Sheet1:

0 0.1
1000 0.2
5000 0.25
10000 0.3
25000 0.35

This says anything =0 and <1000 has a 10% fee, anything =1000 and <5000
has a 20% fee, and so on. From VBA code, you can use the VLOOKUP worksheet
function to get the fee %.

You could use the Exit event routine for the TextBox in order to update 2
Label controls that contain the fee description and the actual fees charged
on the enumeration entered. You can use the VLOOKUP function to get the fee
% for the specified enumeration and then use that in the 2 Labels:

Private Sub txtEnumeration_Exit(ByVal Cancel _
As MSForms.ReturnBoolean)
Dim sngFeePct As Single
Dim sngEnum As Single

If IsNumeric(txtEnumeration.Text) Then
sngEnum = CSng(txtEnumeration.Text)
sngFeePct = Application.WorksheetFunction.VLookup( _
sngEnum, Sheet1.Range("A1:B5"), _
2)
lblFeeExplanation.Caption = "Our standard fee " _
& "for this assignment is " & Format$(sngFeePct, _
"0.00%")
lblFees.Caption = Format$(sngEnum * sngFeePct, _
"$#,##0.00")
Else
MsgBox "Invalid renumeration"
Cancel = True
End If
End Sub

Obviously, you may want to do some more validation to make this more robust.
And you can localize it to use UK currency/decimal separators - I just did
it quickly using US notation.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Graham wrote:
Hi,

I have a user form in a recruitment related business into
which the user inputs the chargeable remuneration of the
candidate e.g £25000 and will then have to select from
several fee options. On entering the chargeable
remuneration, I would like a couple of information boxes
to appear underneath which would say something like

Our standard fee for this assignment is x%(x being a
calculated variable based upon the chargeable remuneration
and contained in a spreadsheet within the same workbook as
the user form)and £y (y=chargeable remuneration multiplied
by x%)

I'd be grateful for any thoughts on the best way to
achieve this.

Thanks
Graham