Code bug help please

peter.thompson · #1 01-01-2006, 02:38 AM

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)

#2 01-01-2006, 08:55 AM

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

#3 01-01-2006, 02:00 PM

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

peter.thompson · #4 01-01-2006, 04:18 PM

Thanks Tushar and Tom, much appreciated!

Cheers

Peter

Welcome to the Excel Forum

Forum Statistics