# Re: Column Limit

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

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
--
Tony Green

"JDMils" wrote:

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

--

|
+-- JDMils
|

"Geoff" <Geoff@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CBA2361A-B139-4394-807F-E8A2BF0564DA@xxxxxxxxxxxxxxxx
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

.

## Relevant Pages

• Re: Column Limit
... Hi Geoff the other option is to write your own Column no function. ... Dim ValidColumn As Boolean ... can I prevent the user inputting a value greater than 'IV'? ... Inputting 'K' yields 11 in the test msgbox ...
(microsoft.public.excel.programming)
• Re: Column Limit
... Thank you and again to JDMils for the more general trap. ... Dim ValidColumn As Boolean ... can I prevent the user inputting a value greater than 'IV'? ... Inputting 'K' yields 11 in the test msgbox ...
(microsoft.public.excel.programming)