VBA - Accept Column Letters Only as INPUT

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
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,
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Dec 29, 2006
Messages
622
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try this

Code:
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

Well-known Member
Joined
Dec 29, 2006
Messages
622

ADVERTISEMENT

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

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

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
just change
MyCol = Application.InputBox("Enter column Letter", , , , , , , 2)
to
MyCol = Range("D7").Value
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,691
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Try this

Code:
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..
Code:
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

Well-known Member
Joined
Dec 29, 2006
Messages
622
Jonmo1, this doesn't seem to work for me...
 
Last edited:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,691
Office Version
  1. 2010
Platform
  1. Windows
Here is another way to test without setting a range and without error trapping..
Code:
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...
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,893
Messages
5,598,712
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top