Re: Code bug help please

Tech-Archive recommends: Speed Up your PC by fixing your registry

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@xxxxxxxxxxxxxxxxxxxxx> wrote in
message news:peter.thompson.20y3om_1136101201.8501@xxxxxxxxxxxxxxxxxxxxxxxx
>
> 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?action=getinfo&userid=29686
> View this thread: http://www.excelforum.com/showthread.php?threadid=497225
>


.

Quantcast