dcsimg
Results 1 to 12 of 12

Thread: VBA - Adding Dashes Automatically Into Text Box

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Nov 2005
    Posts
    23

    VBA - Adding Dashes Automatically Into Text Box

    Hi,

    I'm looking to add dashes automatically into a text box.

    The text box is used for entering a number in this format: ###-###-###

    So the user enters into textbox: 123456789
    And it automatically becomes: 123-456-789

    How do I add the dashes automatically? I totally lost.

    Using VBA in Excel 2010.

    Thanks!

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,558

    Re: VBA - Adding Dashes Automatically Into Text Box

    This would do it AFTER they've entered 9 characters:

    Code:
    Private Sub txtNumber_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        Dim strTemp As String
        
        If txtNumber.TextLength = 9 Then
            strTemp = txtNumber.Text
            strTemp = Left(strTemp, 3) & "-" & Mid(strTemp, 4, 3) & "-" & Right(strTemp, 3)
            txtNumber.Text = strTemp
        End If
    End Sub
    If you wanted it to put the dashes in as they type, that's a different story.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Nov 2005
    Posts
    23

    Re: VBA - Adding Dashes Automatically Into Text Box

    Quote Originally Posted by vbfbryce View Post
    This would do it AFTER they've entered 9 characters:

    Code:
    Private Sub txtNumber_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        Dim strTemp As String
        
        If txtNumber.TextLength = 9 Then
            strTemp = txtNumber.Text
            strTemp = Left(strTemp, 3) & "-" & Mid(strTemp, 4, 3) & "-" & Right(strTemp, 3)
            txtNumber.Text = strTemp
        End If
    End Sub
    If you wanted it to put the dashes in as they type, that's a different story.
    Thanks a lot. It works somewhat.

    I have a couple issues however.

    First, I am running a function to limit the textbox to only accept numbers.

    Code:
    Private Sub OnlyNumbers()
    
        If TypeName(Me.ActiveControl) = "TextBox" Then
            With Me.ActiveControl
                If Not IsNumeric(.value) And .value <> vbNullString Then
                    .value = vbNullString
                End If
            End With
        End If
    
    End Sub
    This code kind of conflicts with adding dashes, since it will empty the textbox after 9 digits are entered. If I use the code to add functions, dash is inserted into the textbox, and my NumbersOnly function will empty the textbox.

    I tried to make an exception for the dashes in my code above but it doesn't seem to work.

    Also, your code seems enters dashes when I press backspace. For example, after the dashes are entered, and I press backspace, when the textbox length gets to 9, it adds dashes. So if I hold down backspace, I will get textboxt full of "-----------".

    Also, I use the NumbersOnly function for multiple textboxes where I need only number inputs. So I don't want to change this function and have other textboxes compromised.

    Thanks for the help.

  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,558

    Re: VBA - Adding Dashes Automatically Into Text Box

    How are you calling that sub?

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Nov 2005
    Posts
    23

    Re: VBA - Adding Dashes Automatically Into Text Box

    Code:
    Private Sub TxtSIN_Change()
    
        OnlyNumbers
         
    End Sub

  6. #6
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,558

    Re: VBA - Adding Dashes Automatically Into Text Box

    Maybe use the KeyUp event to "swallow invalid characters," then use the Change event to add the dashes, something like this:

    Code:
    Private Sub TextBox1_Change()
        If TextBox1.TextLength = 9 Then
            Call addDashes(TextBox1)
        End If
    End Sub
    
    Private Sub addDashes(tb As MSForms.TextBox)
        Dim strTemp2 As String
        
        strTemp2 = Left(tb.Text, 3) & "-" & Mid(tb.Text, 4, 3) & "-" & Right(tb.Text, 3)
        tb.Text = strTemp2
    End Sub
    
    Private Sub OnlyNumbers()
        Dim strTemp As String
        
        With Me
            If TypeName(.ActiveControl) = "TextBox" Then
                If Len(.ActiveControl.Value) > 0 Then
                    strTemp = .ActiveControl.Value
                    If Not IsNumeric(Right(strTemp, 1)) Then
                        .ActiveControl.Value = Left(strTemp, Len(strTemp) - 1)
                    End If
                End If
            End If
        End With
    End Sub
    
    Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        Call OnlyNumbers
    End Sub

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Nov 2005
    Posts
    23

    Re: VBA - Adding Dashes Automatically Into Text Box

    Code:
    Private Sub TxtSIN_Change()
    
        OnlyNumbers
         
    End Sub

  8. #8
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,558

    Re: VBA - Adding Dashes Automatically Into Text Box

    Not sure why you posted that again...did you read my suggestion in the previous post?

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Nov 2005
    Posts
    23

    Re: VBA - Adding Dashes Automatically Into Text Box

    Quote Originally Posted by vbfbryce View Post
    Not sure why you posted that again...did you read my suggestion in the previous post?
    Sorry, the forum logged me out.

    Thanks, that code works.

    Just one thing, I can't use backspace when deleting numbers from the textbox. It will just add dashes as I hold down backspace.

  10. #10
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,558

    Re: VBA - Adding Dashes Automatically Into Text Box

    If you don't require that the dashes are immediately removed when you hit the backspace key, this should do it:

    Code:
    Dim backSpacePressed As Boolean
    
    Private Sub TextBox1_Change()
        If TextBox1.TextLength = 9 And backSpacePressed = False Then
            Call addDashes(TextBox1)
        End If
    End Sub
    
    Private Sub addDashes(tb As MSForms.TextBox)
        Dim strTemp2 As String
        
        strTemp2 = Left(tb.Text, 3) & "-" & Mid(tb.Text, 4, 3) & "-" & Right(tb.Text, 3)
        tb.Text = strTemp2
    End Sub
    
    Private Sub OnlyNumbers()
        Dim strTemp As String
        
        With Me
            If TypeName(.ActiveControl) = "TextBox" Then
                If Len(.ActiveControl.Value) > 0 Then
                    strTemp = .ActiveControl.Value
                    If Not IsNumeric(Right(strTemp, 1)) Then
                        .ActiveControl.Value = Left(strTemp, Len(strTemp) - 1)
                    End If
                End If
            End If
        End With
    End Sub
    
    Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If KeyCode <> 8 Then
            backSpacePressed = False
            Call OnlyNumbers
        Else
            backSpacePressed = True
        End If
    End Sub

  11. #11
    New Member
    Join Date
    Aug 2019
    Posts
    1

    Re: VBA - Adding Dashes Automatically Into Text Box

    Quote Originally Posted by vbfbryce View Post
    This would do it AFTER they've entered 9 characters:

    Code:
    Private Sub txtNumber_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        Dim strTemp As String
        
        If txtNumber.TextLength = 9 Then
            strTemp = txtNumber.Text
            strTemp = Left(strTemp, 3) & "-" & Mid(strTemp, 4, 3) & "-" & Right(strTemp, 3)
            txtNumber.Text = strTemp
        End If
    End Sub
    If you wanted it to put the dashes in as they type, that's a different story.
    This is an old post but this might help someone; To add dashes on the fly is not much different:


    Code:
    Private Sub TextBox1_Change()
    
    TextBox1 = UCase(TextBox1)
    
        If TextBox1.TextLength = 3 Then
            Call addDashes(TextBox1)
        End If
        
        If TextBox1.TextLength = 6 Then
            Call addDashes2(TextBox1)
        End If
    
    End Sub
    
    Private Sub addDashes(tb As MSForms.TextBox)
        Dim strTemp2 As String
        strTemp2 = Left(tb.Text, 3) & "-" & Right(tb.Text, 0)
        tb.Text = strTemp2
    End Sub
    
    Private Sub addDashes2(tb As MSForms.TextBox)
        Dim strTemp2 As String
        strTemp2 = Left(tb.Text, 7) & "-" & Right(tb.Text, 0)
        tb.Text = strTemp2
    End Sub

  12. #12
    Fanatic Member
    Join Date
    Nov 2017
    Posts
    791

    Re: VBA - Adding Dashes Automatically Into Text Box

    Mentally test driving your code, what happens if, after I enter 123 I realize that I should have entered 124?

    Stepping it out.

    I type 1
    I type 2
    I type 3
    The code inserts a dash after the three.
    Whoops! I should have typed 4. So I press backspace.
    The dash is removed, the textbox change is triggered, len = 3, the dash is re-added
    Endless loop unless additional steps are taken to work around the code


    Or if I type 123 and realize it should have been 133:

    I type 1
    I type 2
    I type 3
    The code inserts a dash after the three.
    Whoops! I should have typed a 3 instead of a 2.
    I move the cursor to the right of the 2 and press backspace
    The 2 is removed, the textbox change is triggered, len = 3, now the textbox says 13--


    In the second case:

    I'm trying to type 123456789 to get 123-456-789

    I type 1
    I type 2
    I type 3
    Len = 3, so a dash is added
    I type 4
    I type 5
    Len=6, so a dash is added
    I type 6
    I type 7
    I type 8
    I type 9


    Now the typed text is 123-45-6789, but should have been 123-456-789

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width