Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking usewr has input a valid address
Good afternoon all (again)
The object of the exercise is to check that the user has input a valid address (as I'm FAIRLY sure it's not possible to check if a named range exists in a workbook without opening it). Sub ATestForValidRange() On Error Resume Next MyValue = InputBox("Enter a value:", "Test for valid range") Set MyRange = Range(MyValue) On Error GoTo 0 If Not MyRange Is Nothing Then MsgBox ("Valid Range") Else MsgBox ("Invalid Range") End If End Sub This code functions OK, providing you input a valid range such as a5, $a$5:$p$26 etc, but if you put in something like "25", it falls over with the message"Object required" on the "If not myrange is nothing" line, instead of correctlty branching to display an error message. Can anyone out there help, please? Thanks in advance Pete |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking usewr has input a valid address
Try this instead:
If TypeName(myRange) < "Empty" Then "Peter Rooney" wrote: Good afternoon all (again) The object of the exercise is to check that the user has input a valid address (as I'm FAIRLY sure it's not possible to check if a named range exists in a workbook without opening it). Sub ATestForValidRange() On Error Resume Next MyValue = InputBox("Enter a value:", "Test for valid range") Set MyRange = Range(MyValue) On Error GoTo 0 If Not MyRange Is Nothing Then MsgBox ("Valid Range") Else MsgBox ("Invalid Range") End If End Sub This code functions OK, providing you input a valid range such as a5, $a$5:$p$26 etc, but if you put in something like "25", it falls over with the message"Object required" on the "If not myrange is nothing" line, instead of correctlty branching to display an error message. Can anyone out there help, please? Thanks in advance Pete |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking usewr has input a valid address
Martin,
Spot on. Have a green tick and my thanks! :-) Pete "Martin" wrote: Try this instead: If TypeName(myRange) < "Empty" Then "Peter Rooney" wrote: Good afternoon all (again) The object of the exercise is to check that the user has input a valid address (as I'm FAIRLY sure it's not possible to check if a named range exists in a workbook without opening it). Sub ATestForValidRange() On Error Resume Next MyValue = InputBox("Enter a value:", "Test for valid range") Set MyRange = Range(MyValue) On Error GoTo 0 If Not MyRange Is Nothing Then MsgBox ("Valid Range") Else MsgBox ("Invalid Range") End If End Sub This code functions OK, providing you input a valid range such as a5, $a$5:$p$26 etc, but if you put in something like "25", it falls over with the message"Object required" on the "If not myrange is nothing" line, instead of correctlty branching to display an error message. Can anyone out there help, please? Thanks in advance Pete |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking usewr has input a valid address
The better way to improve your code is to declare myrange as range, then it
will work as originally intended. Sub ATestForValidRange() Dim MyRange as Range On Error Resume Next MyValue = InputBox("Enter a value:", "Test for valid range") Set MyRange = Range(MyValue) On Error GoTo 0 If Not MyRange Is Nothing Then MsgBox ("Valid Range") Else MsgBox ("Invalid Range") End If End Sub -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Martin, Spot on. Have a green tick and my thanks! :-) Pete "Martin" wrote: Try this instead: If TypeName(myRange) < "Empty" Then "Peter Rooney" wrote: Good afternoon all (again) The object of the exercise is to check that the user has input a valid address (as I'm FAIRLY sure it's not possible to check if a named range exists in a workbook without opening it). Sub ATestForValidRange() On Error Resume Next MyValue = InputBox("Enter a value:", "Test for valid range") Set MyRange = Range(MyValue) On Error GoTo 0 If Not MyRange Is Nothing Then MsgBox ("Valid Range") Else MsgBox ("Invalid Range") End If End Sub This code functions OK, providing you input a valid range such as a5, $a$5:$p$26 etc, but if you put in something like "25", it falls over with the message"Object required" on the "If not myrange is nothing" line, instead of correctlty branching to display an error message. Can anyone out there help, please? Thanks in advance Pete |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking usewr has input a valid address
Tom,
That's neat. Thank you, and a green tick for YOU, too! :-) Regards Pete "Tom Ogilvy" wrote: The better way to improve your code is to declare myrange as range, then it will work as originally intended. Sub ATestForValidRange() Dim MyRange as Range On Error Resume Next MyValue = InputBox("Enter a value:", "Test for valid range") Set MyRange = Range(MyValue) On Error GoTo 0 If Not MyRange Is Nothing Then MsgBox ("Valid Range") Else MsgBox ("Invalid Range") End If End Sub -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Martin, Spot on. Have a green tick and my thanks! :-) Pete "Martin" wrote: Try this instead: If TypeName(myRange) < "Empty" Then "Peter Rooney" wrote: Good afternoon all (again) The object of the exercise is to check that the user has input a valid address (as I'm FAIRLY sure it's not possible to check if a named range exists in a workbook without opening it). Sub ATestForValidRange() On Error Resume Next MyValue = InputBox("Enter a value:", "Test for valid range") Set MyRange = Range(MyValue) On Error GoTo 0 If Not MyRange Is Nothing Then MsgBox ("Valid Range") Else MsgBox ("Invalid Range") End If End Sub This code functions OK, providing you input a valid range such as a5, $a$5:$p$26 etc, but if you put in something like "25", it falls over with the message"Object required" on the "If not myrange is nothing" line, instead of correctlty branching to display an error message. Can anyone out there help, please? Thanks in advance Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checking hyperlinks are valid | Excel Discussion (Misc queries) | |||
The address of this site is not valid. Check the address and try | Excel Discussion (Misc queries) | |||
checking hyperlinks are valid | Excel Discussion (Misc queries) | |||
Checking user has input a valid address. | Excel Programming | |||
Checking If Hyperlink Is Valid | Excel Programming |