Hi this is a 2 part question. Again - im new to this so please be
patient. :)

1 - Would anyone be able to explain to me how to get excel to ignore
the "$" symbol if it is entered in a text box? The situation is:
there is a userform with many boxes one of them is "Salary" text box
(tbxSal)..
The user enters the salary and this is then used in a calculation.
I anticipate that it will be very common for users to include a $.
Therefore I dont want to use an error message rather I would just
prefer excel to just ignore that character and continue with the calc.
How would I do this?

2 - And send up an error message if a letter or other character is used
rather than the "do you wish to debug" thing. If a letter eg "a" is
entered it throws this up, I assume because Im using the IsNumeric
thing.

here is the relevant code.
Thanks in advance.
B

Private Sub tbxSal_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Calcs Income Protection Benefits and premium and "DEATH ONLY/TPD + IP"
total benefits
' and recalculates if salary changed.



If tbxSal < "" Then
If IsNumeric(tbxSal) = True Then
Dim BenN As Double
Dim BenS As Double


maxben = Range("Admin!B10")
MaxSal = Round((maxben / (((Range("Admin!C10") +
Range("Admin!D10")) / 100))), 2)
BenN = Round(((tbxSal * ((Range("Admin!C10") / 100))) / 52),
2)
BenS = Round(((tbxSal * ((Range("Admin!D10") / 100))) / 52),
2)

If tbxSal MaxSal Then
BenN = Round(((MaxSal * ((Range("Admin!C10") / 100))) / 52),
2)
BenS = Round(((MaxSal * ((Range("Admin!D10") / 100))) / 52),
2)
End If

tbxWkBenN = FormatCurrency(BenN, 2)
tbxWkBenS = FormatCurrency(BenS, 2)
tbxWkBenT = FormatCurrency((BenN + BenS), 2)
End If

End If



If IsNumeric(tbxSal) = True Then

If cbxOcCat < "" Then
Dim Col As String 'Premium Rate Column
Dim Row As String 'Age row
Dim PR As String 'Cell location containing Premium Rate


Row = cbxAge - 11

If cbxSex = "Male" Then
Select Case cbxOcCat
Case Is = ("White Collar")
Col = "B"
Case Is = ("Light Blue Collar")
Col = "D"
Case Else
Col = "F"
End Select

ElseIf cbxSex = "Female" Then
Select Case cbxOcCat
Case Is = ("White Collar")
Col = "C"
Case Is = ("Light Blue Collar")
Col = "E"
Case Else
Col = "G"
End Select

End If

Maybe just let them enter numbers.

Option Explicit
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Select Case KeyAscii
Case Asc("0") To Asc("9")
'do nothing
Case Else
Beep
KeyAscii = 0
End Select

End Sub

You could format the value after they leave the textbox.

B wrote:

Hi this is a 2 part question. Again - im new to this so please be
patient. :)

1 - Would anyone be able to explain to me how to get excel to ignore
the "$" symbol if it is entered in a text box? The situation is:
there is a userform with many boxes one of them is "Salary" text box
(tbxSal)..
The user enters the salary and this is then used in a calculation.
I anticipate that it will be very common for users to include a $.
Therefore I dont want to use an error message rather I would just
prefer excel to just ignore that character and continue with the calc.
How would I do this?

2 - And send up an error message if a letter or other character is used
rather than the "do you wish to debug" thing. If a letter eg "a" is
entered it throws this up, I assume because Im using the IsNumeric
thing.

here is the relevant code.
Thanks in advance.
B

Private Sub tbxSal_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Calcs Income Protection Benefits and premium and "DEATH ONLY/TPD + IP"
total benefits
' and recalculates if salary changed.

If tbxSal < "" Then
If IsNumeric(tbxSal) = True Then
Dim BenN As Double
Dim BenS As Double

maxben = Range("Admin!B10")
MaxSal = Round((maxben / (((Range("Admin!C10") +
Range("Admin!D10")) / 100))), 2)
BenN = Round(((tbxSal * ((Range("Admin!C10") / 100))) / 52),
2)
BenS = Round(((tbxSal * ((Range("Admin!D10") / 100))) / 52),
2)

If tbxSal MaxSal Then
BenN = Round(((MaxSal * ((Range("Admin!C10") / 100))) / 52),
2)
BenS = Round(((MaxSal * ((Range("Admin!D10") / 100))) / 52),
2)
End If

tbxWkBenN = FormatCurrency(BenN, 2)
tbxWkBenS = FormatCurrency(BenS, 2)
tbxWkBenT = FormatCurrency((BenN + BenS), 2)
End If

End If

If IsNumeric(tbxSal) = True Then

If cbxOcCat < "" Then
Dim Col As String 'Premium Rate Column
Dim Row As String 'Age row
Dim PR As String 'Cell location containing Premium Rate

Row = cbxAge - 11

If cbxSex = "Male" Then
Select Case cbxOcCat
Case Is = ("White Collar")
Col = "B"
Case Is = ("Light Blue Collar")
Col = "D"
Case Else
Col = "F"
End Select

ElseIf cbxSex = "Female" Then
Select Case cbxOcCat
Case Is = ("White Collar")
Col = "C"
Case Is = ("Light Blue Collar")
Col = "E"
Case Else
Col = "G"
End Select

End If

--

Dave Peterson

Woohoo!! Awesome - thats an even better solution! Thanks Dave!
Im soooooo glad I found this place.....not that it was hard to find.

bernie