Using 'RefEdit' control to check specific range

Question

I have a user form in which the user is asked to select a range of cells using a RefEdit control. I am trying to store the corresponding range in a range variable so I can run several checks on that range (no empty cells, valid inputs, ...). For now I've tried to do it using (as I've seen lots of people doing it that way on the Internet):

Dim Data_Range as Range
Set Data_Range = Range(RefEdit_DataRange.Value)

but it only ends with an error:

'Error 1004: Method "Range" for object "_Global" failed. (approximate translation)

I printed RefEdit_DataRange.Value in a MsgBox and it's a string formatted Sheet!Cell1:Cell2 so it looks like what I was expecting but it's not working.

Did anyone ever run into that same issue?

R3uK R3uK · Accepted Answer · 2015-11-23T12:36:03

You'll need to pass your sheet reference before the range address, from what you have, the split seems the easiest way to do it :

Dim Data_Range As Range, _
    A() As String

A = Split(RefEdit_DataRange.Value, "!")
Set Data_Range = Sheets(replace(A(0),Chr(39),"")).Range(A(1))

Using 'RefEdit' control to check specific range

1 Answers