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,

 

[bs0d]

Dim MyTestRange As Range, MyCol As String
For chk4 = LBound(varRef) To UBound(varRef)

    MyCol = Range(varRef(chk4)).Value
        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

Next chk4

varRef() is an array containing the list of cell references to check for non-Alpha values. (such as "D7", "D11", "E15" ...).

If a non-Alpha character is in the first refeference in the array, it catches it (produces "invalid entry" result). However, it will not catch any others (produces "valid entry" result).

 

[bs0d]

[delete]

 

[Jonmo1]

Was unaware this was going into a loop.
You need to reset the MyTestRange to Nothing And reset Error Checking after the IF statement in the loop..

Try

Dim MyTestRange As Range, MyCol As String
For chk4 = LBound(varRef) To UBound(varRef)

    MyCol = Range(varRef(chk4)).Value
    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
    Set MyTestRange = Nothing
    On Error GoTo 0
Next chk4

 

[bs0d]

Thanks jonmo1. It works - can you tell me how essentially Range(MyCol & 1) and if Not MyTestRange Is Nothing flags non alpha characters?

Rick, I'm sure your solution works great. It seems a bit complex though. I'm not sure of the advantages & disadvantages between the solutions.

 

[Jonmo1]

Strictly speaking it's not really flagging NonAlpha characters..
It's really verifying VALID Column References..

Range(MyCol & 1)
The & symbol is concatenate. It joins 2 strings to gether into 1 string

So if MyCol = G
Then it translates to
Range("G1") --> A VALID range reference

If MyCol = a number say 7
Then it ranslates to
Range("71") --> AN INvalid range reference.

Then the
If Not MyTestRange Is Nothing
just tests if it's a valid range reference or not.

This solution is not fool proof of course (nothing really is)
YOu could have an entry of say A$ or $A
It would translate to
Range("A$1") or Range("$A1") which ARE Valid range references.

I believe the $ would be the only non alpha character it would accept, but only if it is accompanied by a valid column letter.

 

[bs0d]

Gotcha, that helps me understand. Throwing in a catch for "$" shouldn't be an issue. Thanks again

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[jeff_on_Mr_Excel]

What about this?

Evaluate("ISREF(" & inputValueHere & "1)")

 

[Rick Rothstein]

What about this?

Evaluate("ISREF(" & inputValueHere & "1)")

That works as long as letters are input, but if letters followed by a number are input, it will also indicate True (for example, if inputValueHere were AB12).