| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Limit
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Column Limit
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Column Limit
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Column Limit
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Column Limit
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Column Limit
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I limit sorting a column to the column? | Excel Worksheet Functions | |||
| Will the column limit be over 256? | Excel Discussion (Misc queries) | |||
| limit column entries | Excel Worksheet Functions | |||
| Column limit for exporting | Excel Discussion (Misc queries) | |||
| Column Limit | Excel Programming | |||
Linear Mode
