brettdj
02-02-2005, 02:02 AM
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?
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?