Code bug help please
 

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

--
peter.thompso
-----------------------------------------------------------------------
peter.thompson's Profile: http://www.excelforum.com/member.php...fo&userid=2968
View this thread: http://www.excelforum.com/showthread.php?threadid=49722

Code bug help please
 

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

Code bug help please
 

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.20y3om_1136101201.8501@excelforu m-nospam.com wrote in
message news:peter.thompson.20y3om_1136101201.8501@excelfo rum-nospam.com...

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

Code bug help please
 

Thanks Tushar and Tom, much appreciated!

Cheers

Peter


--
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