Column Limit

[Guest]

Hi
I have a textbox on a form and want the user to input a column header. How
can I prevent the user inputting a value greater than 'IV' ?

Inputting 'K' yields 11 in the test msgbox
Inputting 'KK' gives a 'Type Mismatch' error.

Private Sub txt1_Change()

txt1.Text = UCase(txt1.Text)

''' for syntax testing only
MsgBox Sheets(1).Columns(txt1.Text).Column

If Not Sheets(1).Columns(txt1.Text).Column > 256 Then
''' do something
Else
MsgBox "No such column"
End If

End Sub

T.I.A.

Geoff

 

[JDMils]

Off the top of my head:

On Error Resume Next
Debug.Print Sheets(1).Columns(colTxt).Column
If Err.Number = 13 Then
' Not a valid reference.
Debug.Print "Error"
Else
' Valid Reference.
Debug.Print "OK"
End If
On Error GoTo 0

 

[Guest]

Hi Geoff the other option is to write your own Column no function. The belew
either returns 0 if there is an error or the column number. Its ugly but works

Public Function ColNo(Txt1 As String) As Long
Dim ValidColumn As Boolean
Dim x As Integer
Dim uLimit As String

Txt1 = UCase(Txt1)
ValidColumn = True
If ((Len(Txt1) > 2) Or (Len(Txt1) = 0)) Then
ValidColumn = False
Else
For x = 1 To Len(Txt1)
If ((Mid$(Txt1, x, 1) > "Z") Or (Mid$(Txt1, x, 1) < "A")) Then
ValidColumn = False
Next
End If
If ValidColumn = False Then
ColNo = 0
Else
If Len(Txt1) = 2 Then
ColNo = ((Asc(Left$(Txt1, 1)) - 64) * 26) + (Asc(Right$(Txt1, 1)) -
64)
Else
ColNo = Asc(Txt1) - 64
End If
If ColNo > 256 Then ColNo = 0
End If
End Function

 

[Guest]

Hi
I agree with your suggestion though I am still curious - it seems odd that
the construct:
'If Not Sheets(1).Columns(txt1.Text).Column > 256 Then..... Else'
does not trap a terminal Type Mismatch when lesser values are accepted.
'Subscript out of range' or similar would seem more appropriate at this stage
of my pondering.

Geoff

 

[Guest]

Hi ADG

Your response beat my reply back to JDMils

I will have a look and adapt as I have a total of 7 txtboxes for input not
just one as per my post.

Thanks to both so far.

Geoff

 

[Guest]

Hi ADG

Adapted succesfully. As you said, kludge or not, it works fine and the
solution provides a specific parameter for use elsewhere.

Thank you and again to JDMils for the more general trap.

Geoff