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" <Geoff@discussions.microsoft.com> wrote in message
news:CBA2361A-B139-4394-807F-E8A2BF0564DA@microsoft.com...
> 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" <Geoff@discussions.microsoft.com> wrote in message
> news:CBA2361A-B139-4394-807F-E8A2BF0564DA@microsoft.com...
> > 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" <Geoff@discussions.microsoft.com> wrote in message
> news:CBA2361A-B139-4394-807F-E8A2BF0564DA@microsoft.com...
> > 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" <Geoff@discussions.microsoft.com> wrote in message
> > news:CBA2361A-B139-4394-807F-E8A2BF0564DA@microsoft.com...
> > > 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" <Geoff@discussions.microsoft.com> wrote in message
> > > news:CBA2361A-B139-4394-807F-E8A2BF0564DA@microsoft.com...
> > > > 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
> > >
> > >
> > >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks