VBA - Accept Column Letters Only as INPUT

[bs0d]

I'm creating a macro that requires user input into pre-defined cell areas. I need only column letters from the user. In the VBA code, I'm screening the input for invalid entries.

Rather than write many IF conditions checking for blanks, numeric, length issues, etc. What would be the best approach to detect either Alpha-only or max 3 ALPHA character entries?

My problem is detecting any special characters before the macro runs.

Thanks,

 

[Leith Ross]

Hello bs0d,

It would be easier to answer your question if you provided examples of what valid inputs look like and the special characters that are allowed.

 

[bs0d]

Sorry if I wasn't very clear on that...

Valid input = any Excel column letter (A, B, C ... AA, BB, CC ... AAA , BBB, CCCC; Max = XFD).
Invalid input = anything else. "A ", "!", "9Z", "_B", " R " ...

I just want to be able to error out if anything other than a letter (corresponding to an excel column) is entered since the macro would eventually error out anyway.

I'm sure RegEx can be used, but I don't have a lot of experience with it, particularly in VBA.

 

[Jonmo1]

Try this

Sub test()
Dim MyTestRange As Range, MyCol As String
MyCol = Application.InputBox("Enter column Letter", , , , , , , 2)
On Error Resume Next
Set MyTestRange = Range(MyCol & 1)
If Not MyTestRange Is Nothing Then
    MsgBox "valid column entry"
Else
    MsgBox "INvalid column entry"
End If
End Sub

 

[bs0d]

Can you convert that logic to a cell reference? For instance, I need to evaluate a user entry in cell D7.

Example:

IF Range("D7").Value <> {Alpha Letter} THEN
   z = msgbox("Error, only letters are accepted. Enter excel column letter.", vbOkOnly, "Error")
   end
END IF

 

[Jonmo1]

just change
MyCol = Application.InputBox("Enter column Letter", , , , , , , 2)
to
MyCol = Range("D7").Value

 

[Rick Rothstein]

Try this

Sub test()
Dim MyTestRange As Range, MyCol As String
MyCol = Application.InputBox("Enter column Letter", , , , , , , 2)
On Error Resume Next
Set MyTestRange = Range(MyCol & 1)
If Not MyTestRange Is Nothing Then
    MsgBox "valid column entry"
Else
    MsgBox "INvalid column entry"
End If
End Sub

Here is another way to test without setting a range and without error trapping..

Sub test()
    Dim MyTestRange As Range, MyCol As String
    MyCol = UCase(Right("@@@" & Application.InputBox("Enter column Letter", , , , , , , 2), 3))
    If 576 * (Asc(Left(MyCol, 1)) - 64) + 26 * (Asc(Mid(MyCol, 2, 1)) - 64) + Asc(Right(MyCol, 1)) < 14048 Then
        MsgBox "Valid column entry", vbExclamation
    Else
        MsgBox "Invalid column entry", vbCritical
    End If
End Sub

 

[bs0d]

Jonmo1, this doesn't seem to work for me...

 

[Jonmo1]

Jonmo1, this doesn't seem to work for me...

Works for me.

Can you describe "doesn't seem to work" ?
Do you get an error? What error?
What does it NOT do that you expected it to do? Vice Versa?

Post the most recent version of your code that "doesn't seem to work".

 

[Rick Rothstein]

Here is another way to test without setting a range and without error trapping..

Sub test()
    Dim MyTestRange As Range, MyCol As String
    MyCol = UCase(Right("@@@" & Application.InputBox("Enter column Letter", , , , , , , 2), 3))
    If 576 * (Asc(Left(MyCol, 1)) - 64) + 26 * (Asc(Mid(MyCol, 2, 1)) - 64) + Asc(Right(MyCol, 1)) < 14048 Then
        MsgBox "Valid column entry", vbExclamation
    Else
        MsgBox "Invalid column entry", vbCritical
    End If
End Sub

Actually, if we expand the multiplications and perform the arithmetic on the constants, we can simplify the If..Then test slightly...

Sub test()
    Dim MyTestRange As Range, MyCol As String
    MyCol = UCase(Right("@@@" & Application.InputBox("Enter column Letter", , , , , , , 2), 3))
    If 576# * Asc(Left(MyCol, 1)) + 26 * Asc(Mid(MyCol, 2, 1)) + Asc(Right(MyCol, 1)) < 52577 Then
        MsgBox "Valid column entry", vbExclamation
    Else
        MsgBox "Invalid column entry", vbCritical
    End If
End Sub