ExcelBanter - Column Limit

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Column Limit (https://www.excelbanter.com/excel-programming/363188-column-limit.html)

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

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" wrote in message
...
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

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" wrote in message
...
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

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

"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" wrote in message
...
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

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

"ADG" wrote:

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" wrote in message
...
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

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

"Geoff" wrote:

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

"ADG" wrote:

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" wrote in message
...
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