Using a &%$#%$ RefEdit box to work with a multi sheet range - solution or overkill?

[brettdj]

I've been using a RefEdit box to let users select a working range, I've come to the conclusion that working with RefEdit ranges is either the most annoying Excel feauture I've come accross - or I've missed something very simple. Which still makes it the most annoying Excel feature I've come accross

Say I have three sheets
Sheet1: Sheet1
Sheet2: Fred
Sheet3: Cricket Team

and I let the user pick a range with a UserForm RefEdit

If I run code such as

Dim Myrange As Range
Set Myrange = Range(RefEdit1.Text)

then Excel happily copes with a single or multi area on a single sheet
A range such as

'Cricket Team'!$B$10:$B$12,'Cricket Team'!$E$12:$F$14

causes no issues at all

But what if I select the same range over more than one sheet, say all three sheets, something like

'Sheet1:Cricket Team'!$A$9:$A$12,'Sheet1:Cricket Team'!$E$9:$E$12

Excel splutters and spits back nasty words.

Eventuall I worked out a RegExp parsing routine on the basis that a multisheet range followed the patten below and a single sheet wouldn't. So if this pattern was true the selection was a multi sheet range.

'(text1):(text2)'!(text3)
where text1 is the first sheet
text2 is the last sheet
text3 is the range address

This lead to this code which is linked to a RefEdit, RefEdit1

Private Sub CommandButton1_Click()
Dim Myrange As Range
Dim Regex
Dim FirstSheet As Long, SecondSheet As Long
Dim V As Long, K As Long
Set Regex = CreateObject("vbscript.regexp")
Regex.Pattern = "^'([^']*):(.+?)'!.+"
Regex.Global = False
If Regex.Test(RefEdit1.Value) = True Then
'If true thean more than two sheets have been selected
'Parse first sheet index via sheet name
FirstSh = Sheets(Regex.Replace(RefEdit1.Text, "$1")).Index
'Parse last sheet index via sheet name
SecondSh = Sheets(Regex.Replace(RefEdit1.Value, "$2")).Index
Regex.Global = True
Regex.Pattern = "'(.+?):(.+?)'!"
'Parse address by replacing all sheet names
SheetAddress = Regex.Replace(RefEdit1.Value, "")
'Loop through parsed sheets and addresses
For V = FirstSh To SecondSh
MsgBox "You selected " & Sheets(V).Name & " address " & SheetAddress
K = K + 1
Next
Else
MsgBox "You selected a single sheet no problem"
End If
End Sub

So is this overkill or a solution?

[Jacob Hilderbrand]

I would use Application.InputBox to select the range instead of using a RefEdit box. If the user wants to select from multiple sheets then just store the address from the Application.InputBox to a public variable and let them add to it with Union.

[XL-Dennis]

Hi Dave,

Thanks for the update on the subject

I assume You have spend a lot of time on it and the RegExpr-approach seems to be a (overkilling) workaround.

It only confirm my opinion about the RefEdit-control and the fact we should avoid it as much as possible. The funny thing is that there does not exist any third part control (freeware or commercial one) which indicate some kind of issues with it.

Unless You don't want to add an unless lines of error-handling Jake's suggestion is always an option.

No, since there exist no simpe solution with teh REfEdit You have indeed made a nice work

[brettdj]

Thanks Jake, Dennis

What surprised me is how little there seems to be on the web on this issue, I would have thought that the problematic use of the Ctrl function with a RefEdit box would have been flagged before

My workaround looked heavy so my intuition was that there must be an easier way.

The Ctrl function with the RefEdit box is limited anyhow as the user can select a continuous range of sheets but not sheet1 and sheet3.

But I want to use it in my addin so I guess I'll live with my workaround (although I'm yet to address sheet names with a ' inside them - Sheet'1 gets converted to Sheet''1 via VBA so the sheet index returns an invalid code)

Cheers

Dave

Hi Dave,

Thanks for the update on the subject

I assume You have spend a lot of time on it and the RegExpr-approach seems to be a (overkilling) workaround.

It only confirm my opinion about the RefEdit-control and the fact we should avoid it as much as possible. The funny thing is that there does not exist any third part control (freeware or commercial one) which indicate some kind of issues with it.

Unless You don't want to add an unless lines of error-handling Jake's suggestion is always an option.

No, since there exist no simpe solution with teh REfEdit You have indeed made a nice work