InStr

Excel Microsoft XP on Windows 200

I am using a form as a utility for a group of macros and one of the text
filelds on form asks for the column and row to start the searh in ex. b2

I want to be able to check with vb that the user put both a numeric key and
a alpha key, better yet I would like to check that it is a valid address.

Any suggestions...

Thank you in advance....

FGM,

For example:

Dim myRange As Range
On Error GoTo Badstring:
Set myRange = Range(UserForm1.TextBox1.Text)
MsgBox "Good string"
Exit Sub
Badstring:
MsgBox "That was bad."

HTH,
Bernie
MS Excel MVP


"FGM" <[email protected]> wrote in message
news:[email protected]...
> Excel Microsoft XP on Windows 200
>
> I am using a form as a utility for a group of macros and one of the text
> filelds on form asks for the column and row to start the searh in ex. b2
>
> I want to be able to check with vb that the user put both a numeric key and
> a alpha key, better yet I would like to check that it is a valid address.
>
> Any suggestions...
>
> Thank you in advance....

Just check it really is a range

Private Sub CommandButton1_Click()
Dim rng As Range
On Error Resume Next
Set rng = Range(TextBox1.Value)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "Input a valid range (A1, B1,AB99 etc.)"
With TextBox1
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With
End If
End Sub


or use a RefEdit control.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"FGM" <[email protected]> wrote in message
news:[email protected]...
> Excel Microsoft XP on Windows 200
>
> I am using a form as a utility for a group of macros and one of the text
> filelds on form asks for the column and row to start the searh in ex.
b2
>
> I want to be able to check with vb that the user put both a numeric key
and
> a alpha key, better yet I would like to check that it is a valid address.
>
> Any suggestions...
>
> Thank you in advance....

thank you... you guys are great..

"Bob Phillips" wrote:

> Just check it really is a range
>
> Private Sub CommandButton1_Click()
> Dim rng As Range
> On Error Resume Next
> Set rng = Range(TextBox1.Value)
> On Error GoTo 0
> If rng Is Nothing Then
> MsgBox "Input a valid range (A1, B1,AB99 etc.)"
> With TextBox1
> .SelStart = 0
> .SelLength = Len(.Text)
> .SetFocus
> End With
> End If
> End Sub
>
>
> or use a RefEdit control.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "FGM" <[email protected]> wrote in message
> news:[email protected]...
> > Excel Microsoft XP on Windows 200
> >
> > I am using a form as a utility for a group of macros and one of the text
> > filelds on form asks for the column and row to start the searh in ex.
> b2
> >
> > I want to be able to check with vb that the user put both a numeric key
> and
> > a alpha key, better yet I would like to check that it is a valid address.
> >
> > Any suggestions...
> >
> > Thank you in advance....
>
>
>