How do i get user input to accept letters and spaces only?

[geecee]

I have an input box where the user enters a phrase which must contain letters of the alphabet - either upper case or lower case - and spaces only. How do I write a macro to achieve this?

Any help appreciated.

[HansV]

If you use the InputBox function, you can only check the text entered by the user after they have clicked OK, not while they are entering it.
If you want to allow only letters and spaces to be entered, you can create a userform. See the attached sample workbook:

Input.xlsm

[geecee]

If you use the InputBox function, you can only check the text entered by the user after they have clicked OK, not while they are entering it.
If you want to allow only letters and spaces to be entered, you can create a userform. See the attached sample workbook:

Input.xlsm

Sorry I am a bit late in replying to you Hans.

The following displays the characters as they are being keyed in but I am experiencing problems in that I sometimes get a Runtime Error '5': Invalid procedure call or argument message after I enter something incorrectly then re-enter.

Enter a phrase.xlsm

[HansV]

I cannot reproduce this specific problem. Can you provide detailed information on when it occurs?

I do get a message occasionally "You did not end with a space" even though I did, or "You have used invalid character/s" even though I didn't.

I find the macro rather unpleasant, by the way: there is no way to get out of it. Clicking Cancel results in the message "You did not enter anything".

So all in all, I wouldn't use this code myself.

[Rudi]

It's a little better if you just exit the procedure instead of looping it over and over again....

Code: Select all

Sub ENTER_PHRASE()
Dim phrase As String
    
    phrase = InputBox(Prompt:="You may use spaces and one must be at the end, " & _
    "but no other punctuation marks. " & "Please check your entry carefully.", _
          Title:="ENTER THE PHRASE - 170 CHARACTERS MAXIMUM")
 
    For i = 1 To Len(phrase)
        Select Case Asc(Mid(phrase, i, 1))
            Case 32, 48 To 57, 65 To 90, 97 To 122
            Case Else: MsgBox "You have used invalid character/s.", , "WARNING:": Exit Sub
        End Select
    Next

    If Len(phrase) > 170 Then
        MsgBox "You have exceeded 170 characters.", , "WARNING:": Exit Sub
    End If
    If Len(phrase) = 0 Then
        MsgBox "You did not enter anything.", , "WARNING:": Exit Sub
    End If
    If Right(phrase, 1) <> Chr(32) Then
        MsgBox " You did not end with a space.", , "WARNING:": Exit Sub
    End If

    ' Show phrase in cell A1
    Cells(1, "A") = UCase(phrase)

End Sub

[HansV]

One other thing: in your first post, you specified that only letters and spaces were allowed. The ENTER_PHRASE macro also allows digits.

[geecee]

Hans and Rudi.

Thanks for your help. Much appreciated.

You win some, you lose some. Mostly I lose some.

[geecee]

Sorry to bump this up. I believe I have improved on the coding but I have found something interesting that automatically creates an error. When entering short-form terms such as 'thats' for that is, 'theyve' for they have, 'youve' for you have it automatically enters an apostrophe.

This doesn't happen with other short-form terms such as 'its' for it is, 'heres' for here is, 'theres' for there is, 'weve' for we have, 'shouldve' for should have. These are ones I have tested. There may be more.

Any suggestions?

Thanks

Code: Select all

Sub ENTER_YOUR_PHRASE()

Dim CancelTest As Variant
Dim Ret_type As Integer
Dim phrase As String, message As String


showInputBox:
        Range("A1").ClearContents
        
        phrase = Application.InputBox(prompt:="You may use spaces one of which must be at the end. " & _
                 "No other punctuation marks otherwise you will be asked to try again." & _
                 "", Title:="ENTER THE PHRASE - MAXIMUM 170 CHARACTERS", Default:="", Type:=2)
                    
        CancelTest = phrase
        If CancelTest = False Then
            MsgBox "You clicked the Cancel button, Input Box will close.", 64, "Cancel was clicked."
            Exit Sub
        End If
        
        For i = 1 To Len(phrase)
            Select Case Asc(Mid(phrase, i, 1))
                Case 32, 65 To 90, 97 To 122
                Case Else: Ret_type = MsgBox("" & "YOU ENTERED INVALID CHARACTER/S" & _
                           " - LETTERS AND SPACES ONLY." & vbNewLine & vbNewLine & phrase, vbRetryCancel, "WARNING")
                    Select Case Ret_type
                        Case 2: Cells(1, "A").ClearContents: Exit Sub ' Cancel button clicked
                        Case 4: Cells(1, "A").ClearContents: GoTo showInputBox 'Retry button clicked
                    End Select
            End Select
        Next
        
        If Len(phrase) > 170 Then
            Ret_type = MsgBox("" & "YOU ENTERED TOO MANY CHARACTERS - MAXIMUM 170" & _
            vbNewLine & vbNewLine & phrase, vbRetryCancel, "WARNING")
            Select Case Ret_type
                Case 2: Cells(1, "A").ClearContents: Exit Sub ' Cancel button clicked
                Case 4: Cells(1, "A").ClearContents: GoTo showInputBox 'Retry button clicked
            End Select
        ElseIf phrase = "" Then
            Ret_type = MsgBox("" & "YOU LEFT IT BLANK" & phrase, vbRetryCancel, "WARNING")
            Select Case Ret_type
                Case 2: Cells(1, "A").ClearContents: Exit Sub ' Cancel button clicked
                Case 4: Cells(1, "A").ClearContents: GoTo showInputBox 'Retry button clicked
            End Select
        ElseIf Left(phrase, 1) = " " Then
            Ret_type = MsgBox("" & "YOU STARTED WITH A SPACE" & vbNewLine & vbNewLine & _
            phrase, vbRetryCancel, "WARNING")
            Select Case Ret_type
                Case 2: Cells(1, "A").ClearContents ' Cancel button clicked
                Case 4: Cells(1, "A").ClearContents: GoTo showInputBox 'Retry button clicked
            End Select
        ElseIf Right(phrase, 1) <> " " Then
            Ret_type = MsgBox("" & "YOU DIDN'T END WITH A SPACE" & vbNewLine & vbNewLine & _
            phrase, vbRetryCancel, "WARNING")
            Select Case Ret_type
                Case 2: Cells(1, "A").ClearContents ' Cancel button clicked
                Case 4: Cells(1, "A").ClearContents: GoTo showInputBox 'Retry button clicked
            End Select
        Else
            Ret_type = MsgBox("" & "CHECK FOR SPELLING ERRORS AND/OR MISPLACED SPACES. YOU ENTERED" & vbNewLine & vbNewLine & _
            phrase, vbOKCancel, "FINAL CHECK")
            Select Case Ret_type
                Case 1: Cells(1, "A") = UCase(phrase) ' OK button clicked. Show phrase in cell A1
                Case 2: Cells(1, "A").ClearContents ' Cancel button clicked
            End Select
        End If
        
End Sub

[Rudi]

Hi George,

The culprit in question is AutoCorrect, which adds these apostrophes into the words on enter. See image below.

Image 1.png

Since AutoCorrect is personalized on each PC that might use this code, one cannot just take the culprit phrases out of AutoCorrect, so it is best to do it in the code.

The updated code below should resolve this issue...

Code: Select all

Sub ENTER_YOUR_PHRASE()

Dim CancelTest As Variant
Dim Ret_type As Integer
Dim phrase As String, message As String
Dim i As Integer

showInputBox:
    Range("A1").ClearContents

    phrase = Application.InputBox(prompt:="You may use spaces one of which must be at the end. " & _
                "No other punctuation marks otherwise you will be asked to try again." & _
                "", Title:="ENTER THE PHRASE - MAXIMUM 170 CHARACTERS", Default:="", Type:=2)

    CancelTest = phrase
    If CancelTest = False Then
        MsgBox "You clicked the Cancel button, Input Box will close.", 64, "Cancel was clicked."
        Exit Sub
    End If
    
    'The line below removes any curly and straight quotes added by AutoCorrect
    phrase = Replace(Replace(phrase, "'", ""), "’", "")
    
    For i = 1 To Len(phrase)
        Select Case Asc(Mid(phrase, i, 1))
            Case 32, 65 To 90, 97 To 122
            Case Else: Ret_type = MsgBox("" & "YOU ENTERED INVALID CHARACTER/S" & _
                            " - LETTERS AND SPACES ONLY." & vbNewLine & vbNewLine & phrase, _
                                vbRetryCancel + vbExclamation, "WARNING")
                Select Case Ret_type
                    Case 2: Cells(1, "A").ClearContents: Exit Sub    ' Cancel button clicked
                    Case 4: Cells(1, "A").ClearContents: GoTo showInputBox    'Retry button clicked
                End Select
        End Select
    Next

    If Len(phrase) > 170 Then
        Ret_type = MsgBox("" & "YOU ENTERED TOO MANY CHARACTERS - MAXIMUM 170" & _
                    vbNewLine & vbNewLine & phrase, vbRetryCancel + vbExclamation, "WARNING")
        Select Case Ret_type
            Case 2: Cells(1, "A").ClearContents: Exit Sub    ' Cancel button clicked
            Case 4: Cells(1, "A").ClearContents: GoTo showInputBox    'Retry button clicked
        End Select
    ElseIf phrase = "" Then
        Ret_type = MsgBox("" & "YOU LEFT IT BLANK" & phrase, vbRetryCancel + vbExclamation, "WARNING")
        Select Case Ret_type
            Case 2: Cells(1, "A").ClearContents: Exit Sub    ' Cancel button clicked
            Case 4: Cells(1, "A").ClearContents: GoTo showInputBox    'Retry button clicked
        End Select
    ElseIf Left(phrase, 1) = " " Then
        Ret_type = MsgBox("" & "YOU STARTED WITH A SPACE" & vbNewLine & vbNewLine & _
                    phrase, vbRetryCancel + vbExclamation, "WARNING")
        Select Case Ret_type
            Case 2: Cells(1, "A").ClearContents    ' Cancel button clicked
            Case 4: Cells(1, "A").ClearContents: GoTo showInputBox    'Retry button clicked
        End Select
    ElseIf Right(phrase, 1) <> " " Then
        Ret_type = MsgBox("" & "YOU DIDN'T END WITH A SPACE" & vbNewLine & vbNewLine & _
                    phrase, vbRetryCancel + vbExclamation, "WARNING")
        Select Case Ret_type
            Case 2: Cells(1, "A").ClearContents    ' Cancel button clicked
            Case 4: Cells(1, "A").ClearContents: GoTo showInputBox    'Retry button clicked
        End Select
    Else
        Ret_type = MsgBox("" & "CHECK FOR SPELLING ERRORS AND/OR MISPLACED SPACES. YOU ENTERED" & vbNewLine & vbNewLine & _
                    phrase, vbOKCancel + vbExclamation, "FINAL CHECK")
        Select Case Ret_type
            Case 1: Cells(1, "A") = UCase(phrase)    ' OK button clicked. Show phrase in cell A1
            Case 2: Cells(1, "A").ClearContents    ' Cancel button clicked
        End Select
    End If
    
End Sub

[geecee]

Thanks for that Rudi.

Now my final (I hope) question on the subject. What I want to do now is break up the phrase into individual words and spaces and have each of the letters in these words and the spaces entered automatically into individual cells in a grid which is 13 cells wide and ??? deep - depending on the number required to accommodate all the words. The first cell in each row must contain a letter and not a space. Does that make sense?

The attached programme shows what I am looking for in the grid on the right and the macro produces a start as the grid on the left shows. Can this macro be extended to produce the desired result or is what I am asking too far fetched?

Book5(LATEST).xlsm

[HansV]

Here you go:

Code: Select all

Sub Split_Word()
    Dim phrase As String
    Dim words() As String
    Dim word As String
    Dim i As Long
    Dim j As Long
    Dim r As Long
    Dim c As Long
    Dim m As Long
    phrase = UCase("thats why they call it the american dream because " & _
        "you have to be asleep to believe it")
    Application.ScreenUpdating = False
    Range("A1") = phrase
    Range("A7:M19").ClearContents
    ' Split the phrase into words
    words = Split(phrase)
    ' Stsrt in row 7, column 1
    r = 7
    c = 1
    ' Loop through the words
    For i = 0 To UBound(words)
        ' Get the word
        word = words(i)
        ' And its length
        m = Len(word)
        ' If the word is too long...
        If m > 13 Then
            ' ... inform the user and get out
            MsgBox "The word '" & word & "' does not fit!", vbExclamation
            Exit Sub
        End If
        ' If the end of the word extends past column 13...
        If c + m > 14 Then
            ' ... go to the next row and start in column 1 again
            r = r + 1
            c = 1
        End If
        ' Place the characters
        For j = 1 To m
            Cells(r, c).Value = Mid(word, j, 1)
            c = c + 1
        Next j
        ' Increase column to leave a space
        c = c + 1
    Next i
    Application.ScreenUpdating = True
End Sub

[Rudi]

Hans, I was working on this too, but I must admit.... VERY

[geecee]

Hans and Rudi.

Thanks for your help. Much appreciated