Avoid consecutive character input into a textbox

[kelly mort]

I have tried to reverse engineer a vba script to produce the code below here which allows for only alphabets and the characters listed.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With TextBox1
Select Case KeyAscii 
             Case Asc("a") To Asc ("z")
             Case Asc("A") To Asc ("Z")
             Case Asc("-")
             Case Asc(".")
             Case Asc(" ")
             Case Else: KeyAscii =0
End Select 
End With 
End Sub

I want to stop repeated characters of "-", "." or " " being entered. That's :
1. Don't allow "--", ".." or " "
2. "A-B-","A.B." or "A B " is allowed.
3. Don't allow "A -" , "A- " or "A - ". That is, if a hyphen follows a space immediately, remove the space. So "A -" becomes "A-".

And if a space immediately follows a hyphen, remove the space.
So "A- " becomes "A-"

4. After a dot, if I press any key other than the space key and there is no space after the dot, then add a space before registering the pressed key. But if the pressed key is the hyphen, no space should be added.

I know this is very complex to me. But I believe some got answers over here.

Thanks for reading.

Regards
Kelly

 

[KingX]

I'm sure that there's probably a better way to do this, but this seems to be working for me:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    If (KeyAscii > 59 And KeyAscii < 91) _
    Or (KeyAscii > 96 And KeyAscii < 123) _
    Or KeyAscii = 46 Or KeyAscii = 45 _
    Or KeyAscii = 32 Then
    
        If (Right(TextBox1.Value, 1)) = "-" And KeyAscii = 45 Then
            
            KeyAscii = 0


        ElseIf (Right(TextBox1.Value, 1)) = "." And KeyAscii = 46 Then
    
            KeyAscii = 0
        
        ElseIf (Right(TextBox1.Value, 1)) = " " And KeyAscii = 32 Then
    
            KeyAscii = 0
            
        ElseIf (Right(TextBox1.Value, 1)) = " " And KeyAscii = 45 Then
        
            TextBox1.Value = Left(TextBox1.Value, Len(TextBox1.Value) - 1)
            
            If (Right(TextBox1.Value, 1)) = "-" Then
            
                TextBox1.Value = Left(TextBox1.Value, Len(TextBox1.Value) - 1)
            
            End If
         
         ElseIf (Right(TextBox1.Value, 1)) = "-" And KeyAscii = 32 Then
         
            KeyAscii = 0
            
        ElseIf (Right(TextBox1.Value, 1)) = "." And KeyAscii <> 32 Then
        
            TextBox1.Value = Left(TextBox1.Value, Len(TextBox1.Value)) & " "
        
        Else
    
            KeyAscii = KeyAscii
        
        End If
    
    End If

End Sub

 

[KingX]

I'm sure that there's probably a better way to do this, but this seems to be working for me:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    If (KeyAscii > 59 And KeyAscii < 91) _
    Or (KeyAscii > 96 And KeyAscii < 123) _
    Or KeyAscii = 46 Or KeyAscii = 45 _
    Or KeyAscii = 32 Then
    
        If (Right(TextBox1.Value, 1)) = "-" And KeyAscii = 45 Then
            
            KeyAscii = 0

        ElseIf (Right(TextBox1.Value, 1)) = "." And KeyAscii = 46 Then
    
            KeyAscii = 0
        
        ElseIf (Right(TextBox1.Value, 1)) = " " And KeyAscii = 32 Then
    
            KeyAscii = 0
            
        ElseIf (Right(TextBox1.Value, 1)) = " " And KeyAscii = 45 Then
        
            TextBox1.Value = Left(TextBox1.Value, Len(TextBox1.Value) - 1)
            
            If (Right(TextBox1.Value, 1)) = "-" Then
            
                TextBox1.Value = Left(TextBox1.Value, Len(TextBox1.Value) - 1)
            
            End If
         
         ElseIf (Right(TextBox1.Value, 1)) = "-" And KeyAscii = 32 Then
         
            KeyAscii = 0
            
        ElseIf (Right(TextBox1.Value, 1)) = "." And KeyAscii <> 32 Then
        
            TextBox1.Value = Left(TextBox1.Value, Len(TextBox1.Value)) & " "
        
        Else
    
            KeyAscii = KeyAscii
        
        End If
        
    Else 

         KeyAscii = 0
    
    End If

End Sub

Sorry. I wasn't able to edit my post. I forgot to put the last Else statement in the original post. I fixed it in the quote.

 

[Yongle]

Another way would be via TextBox1_Change

Private Sub TextBox1_Change()
    Dim a, b, j
    a = Array("..", "--", " -", "- ")
    b = Array(".", "-", "-", "-")
    For j = 0 To UBound(a)
        TextBox1.Text = Replace(TextBox1.Text, a(j), b(j))
    Next
End Sub

 

[Rick Rothstein]

One drawback for the code posted so far is that it will allow "forbidden" text to be copy/pasted into the TextBox. The following code will not let the user enter a forbidden character or character sequence and it will not permit forbidden text from being copy/pasted into the TextBox...

[table="width: 500"]
[tr]
	[td]Dim LastPosition As Long

Private Sub TextBox1_Change()
  Static LastText As String
  Static SecondTime As Boolean
  If Not SecondTime Then
    With TextBox1
     If .Text Like "*[!A-Za-z. -]*" Or .Text Like "*..*" Or .Text Like "*--*" Or .Text Like "*  *" Then
        Beep
        SecondTime = True
        .Text = LastText
        .SelStart = LastPosition
      Else
        LastText = .Text
      End If
      .Text = Replace(Replace(Replace(.Text, " - ", "-"), "- ", "-"), " -", "-")
    End With
  End If
  SecondTime = False
End Sub

Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  With TextBox1
    LastPosition = .SelStart
    'Place any other MouseDown event code here
  End With
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  With TextBox1
    LastPosition = .SelStart
    'Place any other KeyPress checking code here
  End With
End Sub[/td]
[/tr]
[/table]

 

[Yongle]

Or

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Dim r As Integer, k As Integer:
    With TextBox1
        r = Asc(Right(.Text, 1))
        k = KeyAscii
        Select Case KeyAscii
            Case Asc("a") To Asc("z")
            Case Asc("A") To Asc("Z")
            Case Asc("-"): If r = k Or r = Asc(" ") Then KeyAscii = 0
            Case Asc("."): If r = k Then KeyAscii = 0
            Case Asc(" "): If r = k Or r = Asc("-") Then KeyAscii = 0
            Case Else: KeyAscii = 0
        End Select
    End With
End Sub

 

[Yongle]

@Rick Rothstein - illicit pasting did not even occurred to me

An alternative would be to clear the clipboard when textbox gets focus

In a new standard module

Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function EmptyClipboard Lib "user32" () As Long
Public Declare Function CloseClipboard Lib "user32" () As Long

TextBox code

Private Sub TextBox1_GotFocus()
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    
    Dim r As Integer, k As Integer:
    With TextBox1
        r = Asc(Right(.Text, 1))
        k = KeyAscii
        Select Case KeyAscii
            Case Asc("a") To Asc("z")
            Case Asc("A") To Asc("Z")
            Case Asc("-"): If r = k Or r = Asc(" ") Then KeyAscii = 0
            Case Asc("."): If r = k Then KeyAscii = 0
            Case Asc(" "): If r = k Or r = Asc("-") Then KeyAscii = 0
            Case Else: KeyAscii = 0
        End Select
    End With
End Sub

 

[Rick Rothstein]

@Rick Rothstein - illicit pasting did not even occurred to me

An alternative would be to clear the clipboard when textbox gets focus

But wouldn't that prevent the user from pre-copying and then pasting valid text into the TextBox?

Also, wouldn't the user be able to go to another non-office window and copy forbidden text from there even after the UserForm was shown?

 

[Yongle]

@kelly mort
- I find it easier working with strings rather than Ascii codes, like this

    Dim r As String, k As String
    With TextBox1
        r = Right(.Text, 1)
        k = Chr(KeyAscii)
        Select Case Chr(KeyAscii)
            Case "a" To "z"
            Case "A" To "Z"
            Case "-": If r = k Or r = " " Then KeyAscii = 0
            Case ".": If r = k Then KeyAscii = 0
            Case " ": If r = k Or r = "-" Then KeyAscii = 0
            Case Else: KeyAscii = 0
        End Select
    End With

 

[Yongle]

But wouldn't that prevent the user from pre-copying and then pasting valid text into the TextBox?

Also, wouldn't the user be able to go to another non-office window and copy forbidden text from there even after the UserForm was shown?

@Rick Rothstein - those are 2 good reasons to use your method rather than mine