Validate Textbox entry

[David Seebaran]

Hi all,

Hope everyone that reads this is fine and the person who solves my problem
lives to at least 93 years of age.

Problem: I have a Textbox within a Userform with which I want the user to
input a valid spreadsheet cell address.

At the moment, the code that I have written to check the value within the
Textbox looks to see if the first character is not numeric and the last
character is numeric. The massive hole in this test is that someone could
input 'DAVID3' and it would pass.

Is there a way to check for a valid spreadsheet cell address?

Regards,


David Seebaran.

 

[Dave Peterson]

You could use something like:

Option Explicit
Sub testme()

Dim testRng As Range
Dim myStr As String

myStr = "a1"
'myStr = "Not a Range!"

Set testRng = Nothing
On Error Resume Next
Set testRng = ActiveSheet.Range(myStr)
On Error GoTo 0

If testRng Is Nothing Then
MsgBox "Nope"
Else
MsgBox "Yep"
End If

End Sub

 

[Harald Staff]

Hi David

Private Sub CommandButton1_Click()
Dim R As Range
On Error Resume Next
Set R = Range(TextBox1.Text)
If R Is Nothing Then
MsgBox TextBox1.Text & " is not an address"
Else
MsgBox R.Count & " cells are entered"
End If
End Sub

Note: This will accept "A:A" , "A114" and any named range, therefore the
R.Count test. Simply use R(1) to get to the first cell of those instances.

Did you btw try the RefEdit control that allow you to pick a range also with
the mouse ?

HTH. Best wishes Harald (43 -and no cigarettes the last 8 days<

 

[Tom Ogilvy]

Dim rng as Range
On Error Resume Next
set rng = Range(Textbox1.Text)
On Error goto 0
if rng is nothing then
msgbox "Invalid Range"
End if