Declaring variable as Range or Variant

[tiredofit]

The user brings up a userform which contains a RefEdit box.

The RefEdit box is populated either by selecting a range on the worksheet or typing a value into it.

The variable assigned to the contents of RefEdit is called ChosenCells.

How should ChosenCells be declared? As a Range or Variant?

If I declare it as a range but the user types in a value, it'll crash.

If I declare as a Variant but the user selects a range on the worksheet, the program won't recognise it.

Thanks

 

[RoryA]

What is the user supposed to be doing with the RefEdit? Presumably selecting a range, so you should declare as range and use error handling as necessary.

 

[tiredofit]

What is the user supposed to be doing with the RefEdit? Presumably selecting a range, so you should declare as range and use error handling as necessary.

Yes, the user is meant to be selecting a range (could be just a single cell) but some have asked to be able to type in a value instead.

I tried error handling such as:

If TypeName(ChosenCells) = "Range" Then

and

If TypeName(Range(ChosenCells) = "Range" Then

but neither worked.

 

[RoryA]

On Error Resume Next
Set ChosenCells = Range(refeditcontrol)
On Error Goto 0
If ChosenCells is Nothing then
' it was a value
end if

 

[tiredofit]

On Error Resume Next
Set ChosenCells = Range(refeditcontrol)
On Error Goto 0
If ChosenCells is Nothing then
' it was a value
end if

Thanks!