Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Only allowing numbers in textbox

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Baltimore
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How would I format a textbox to only allow phone #'s? And if the customer enters text the form won't allow them to get to the next form? Here is the code:

    intcurrentrow = Sheets("Phone Table").[a65536].End(xlUp).Offset(1).Row
    On Error GoTo 1:
    Set mycl2 = Sheet3.Columns("A").Find(What:=txtPhoneNumber.Value)
    1:
    If mycl2 Is Nothing Then
    Sheets("Phone Table").Range("A" & intcurrentrow).Value = _
    txtPhoneNumber.Value
    Sheets("Phone Table").Range("B" & intcurrentrow).Value = _
    TxtAddress.Value
    End If
    Me.Hide
    frmPizzaOptions.Show
    End Sub

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-10 05:18, TheMatrixReloaded wrote:
    How would I format a textbox to only allow phone #'s? And if the customer enters text the form won't allow them to get to the next form? Here is the code:

    intcurrentrow = Sheets("Phone Table").[a65536].End(xlUp).Offset(1).Row
    On Error GoTo 1:
    Set mycl2 = Sheet3.Columns("A").Find(What:=txtPhoneNumber.Value)
    1:
    If mycl2 Is Nothing Then
    Sheets("Phone Table").Range("A" & intcurrentrow).Value = _
    txtPhoneNumber.Value
    Sheets("Phone Table").Range("B" & intcurrentrow).Value = _
    TxtAddress.Value
    End If
    Me.Hide
    frmPizzaOptions.Show
    End Sub

    Try something like this for textbox code;



    Private Sub Textbox1_Change()
    '// Allow ONLY Numbers
    '// TextBox number mask
    Dim Curpos As Double
    Curpos = TextBox1.SelStart
    If Not ValidateNumeric(Right(TextBox1.Text, 1)) Then
    TextBox1.Text = Left(TextBox1.Text, Curpos - 1)
    End If
    End Sub

    Private Function ValidateNumeric(strText As String) As Boolean
    ValidateNumeric = CBool(strText = "" Or IsNumeric(strText))
    End Function





    Kind Regards,
    Ivan F Moala From the City of Sails

  3. #3
    New Member
    Join Date
    May 2002
    Location
    Baltimore
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    One problem, I need it to allow the "-" inbetween the numbers such as "410-876-9807"

  4. #4
    New Member
    Join Date
    May 2002
    Location
    Baltimore
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    nevermind, I fixed it. Thanks man

  5. #5
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-10 05:47, TheMatrixReloaded wrote:
    nevermind, I fixed it. Thanks man
    Thats great
    How did you fix it?

    Here is what I came up with.
    Note this handles the event where the user
    may enter -- in a row....


    Private Sub Textbox1_Change()
    '// Allow ONLY Numbers
    '// TextBox number mask
    Dim Curpos As Double
    Curpos = TextBox1.SelStart
    If Curpos = 1 Then
    '// need to handle -- in a row
    If Not ValidateNumeric(Right(TextBox1.Text, 1)) Then
    TextBox1.Text = Left(TextBox1.Text, Curpos - 1)
    End If
    Else
    If Not ValidateNumeric(Right(TextBox1.Text, 1), Mid(TextBox1.Text, Curpos - 1, 1)) Then
    TextBox1.Text = Left(TextBox1.Text, Curpos - 1)
    End If
    End If
    End Sub

    Private Function ValidateNumeric(strText As String, Optional strPrev As String) As Boolean
    ValidateNumeric = CBool(strText = "" Or IsNumeric(strText) _
    Or strText = "-" And strPrev <> "-")
    End Function



    Kind Regards,
    Ivan F Moala From the City of Sails

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •