Code bug help please

[peter.thompson]

I have a userform with several textboxes that I want only numeric dat
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 tak
any alphanumeric characters, hence I have no 'formatting' code for thi
box.

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

Question 2

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


Any help much appreciated and a happy new year to everyone

Cheers

Peter (new to VBA

 

[Tushar Mehta]

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

nospam.com>, peter.thompson.20y3om_1136101201.8501@excelforum-
nospam.com says...

 

[Tom Ogilvy]

No argument with Tushar's recommendation, but if you want to continue with
what you have:

Private Sub OnlyNumbers()
Dim sStr as String
With Me.ActiveControl
' If .Name <> "TextBox1" Then
sStr = Replace(Replace(.Value, ",", ""), "$", "")
If Not IsNumeric(sStr) And sStr <> vbNullString Then
MsgBox "Sorry, only numbers allowed", 64, "Error Message"
.Value = vbNullString
End If
' End If
End With
End Sub

Private Sub txtPrice_Change()
Dim sStr as String
OnlyNumbers
sStr = Replace(Replace(TxtPrice.Value, ",", ""), _
"$", "")
TxtPrice.Text = Format(sStr, "$ #,###")
End Sub

Assuming the alphanumeric textbox is named TextBox1. Change to suit.

--
Regards,
Tom Ogilvy




"peter.thompson"

 

[peter.thompson]

Thanks Tushar and Tom, much appreciated!

Cheers

Peter