refedit controls and R1C1 notation

[Robert Flanagan]

If a worksheet is set to use R1C1 notation, the entry in a refedit control
is in that notation when the user selects a cell. However the following
does not work:

dim selectedR as range
Set selectedR = Range(userform1.refedit1.text)

because of the R1C1 notation. The only solution I see is to check the
notation, set it to A1 notation, display the userform, get the information,
and then set the notation back to R1C1.

Is there a better way?

Bob

 

[Tim Zych]

Hi Bob. How about..

Dim s As String, selectedR As Range
s = UserForm1.RefEdit1.Text
If Application.ReferenceStyle = xlR1C1 Then
Set selectedR = Range(Application.ConvertFormula(s, xlR1C1, xlA1))
Else
Set selectedR = Range(s)
End If

 

[Robert Flanagan]

Thanks Tim. That's a better solution than changing the setting.

Bob