IMO, checking every keystroke is a very user-hostile design. Yes,
people may make mistakes. Give them a chance to correct their own
mistakes. I would associate all my validation with the OK button code.
Here's a structural skeleton:

Option Explicit

Sub doErrorMsg(x As MSForms.TextBox)
MsgBox "Must be a whole number"
x.SetFocus
End Sub
Function CurrencyOnly(x As MSForms.TextBox)
CurrencyOnly = True
If Not IsNumeric(x.Text) Then
doErrorMsg x
CurrencyOnly = False
Else
Dim y As Double
y = CDbl(x.Text)
If y \ 1 < y Then
doErrorMsg x
CurrencyOnly = False
End If
End If
End Function
Private Sub OK_Click()
If CurrencyOnly(Me.TextBox1) Then
Else
End If
End Sub


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <peter.thompson.20y3om_1136101201.8501@excelforu m-
nospam.com, peter.thompson.20y3om_1136101201.8501@excelforum-
nospam.com says...

I have a userform with several textboxes that I want only numeric data
to be eneterd. Here is a sample of the code I have used for this:

Private Sub OnlyNumbers()
With Me.ActiveControl
If Not IsNumeric(.Value) And .Value < vbNullString Then
MsgBox "Sorry, only numbers allowed", 64, "Error Message"
Value = vbNullString
End If
End With
End Sub

Private Sub txtPrice_Change()
OnlyNumbers
txtPrice.Text = Format(txtPrice.Text, "#,##")
End Sub

Question 1

When setting up the tab order, I have set up #1 a textbox that can take
any alphanumeric characters, hence I have no 'formatting' code for this
box.

The problem is I get the 'onlynumbers error message' come up when the
form is opened. If I move the alphanumeric box down to number 2 in the
tab order, the problem goes away!

Question 2

I i want a textbox to contain the format "$ #,##" what do I need to do
to the OnlyNumbers Private Sub to cater for the $ symbol and only
numeric data?


Any help much appreciated and a happy new year to everyone

Cheers

Peter (new to VBA)


--
peter.thompson
------------------------------------------------------------------------
peter.thompson's Profile: http://www.excelforum.com/member.php...o&userid=29686
View this thread: http://www.excelforum.com/showthread...hreadid=497225