brettdj
08-15-2004, 07:53 AM
Hi all,
I'm running a userform that takes the current range selection and places it in a RefEdit control for potential future use in a format macro
There are a couple of aspects that I'm am not 100% happy with
1) I'm looking for a good method to list a multisheet selection. Currently I'm looping through the selected sheets of the ActiveWindow to construct a string and I'm then using a Regular Expression (the ":" is a delimiter" to parse the sheet names for my coding.
Is there a better approach than this - it seems clunky to me?
Sub MakeRefString()
Dim ws As Worksheet, RefString As String
For Each ws In ActiveWindow.SelectedSheets
RefString = RefString & ws.Name & ":"
Next
RefString = RefString & "!" & Selection.Address
MsgBox RefString
End Sub
2) Is there anyway to select a discontinuous sheet range with a RefEdit box? I can only select a continuous range of grouped sheets and it seems to me that this is a significant limitation for a RefEdit control
If the user selects a multi-sheet range then I'm using a Regular Expression to parse the sheet names. Overkill perhaps but I'm prefer RegExp for parsing.
Set Regex = CreateObject("vbscript.regexp")
Regex.Pattern = "'(.+?):(.+?)'(!.+)"
If Regex.Test(RangeRef.Text) = True Then
FirstSh = Sheets(Regex.Replace(RangeRef.Text, "$1")).Index
SecondSh = Sheets(Regex.Replace(RangeRef.Text, "$2")).Index
For V = FirstSh To SecondSh
'do code
Next
Set Regex = Nothing
Else
'code
End If
Cheers
Dave
I'm running a userform that takes the current range selection and places it in a RefEdit control for potential future use in a format macro
There are a couple of aspects that I'm am not 100% happy with
1) I'm looking for a good method to list a multisheet selection. Currently I'm looping through the selected sheets of the ActiveWindow to construct a string and I'm then using a Regular Expression (the ":" is a delimiter" to parse the sheet names for my coding.
Is there a better approach than this - it seems clunky to me?
Sub MakeRefString()
Dim ws As Worksheet, RefString As String
For Each ws In ActiveWindow.SelectedSheets
RefString = RefString & ws.Name & ":"
Next
RefString = RefString & "!" & Selection.Address
MsgBox RefString
End Sub
2) Is there anyway to select a discontinuous sheet range with a RefEdit box? I can only select a continuous range of grouped sheets and it seems to me that this is a significant limitation for a RefEdit control
If the user selects a multi-sheet range then I'm using a Regular Expression to parse the sheet names. Overkill perhaps but I'm prefer RegExp for parsing.
Set Regex = CreateObject("vbscript.regexp")
Regex.Pattern = "'(.+?):(.+?)'(!.+)"
If Regex.Test(RangeRef.Text) = True Then
FirstSh = Sheets(Regex.Replace(RangeRef.Text, "$1")).Index
SecondSh = Sheets(Regex.Replace(RangeRef.Text, "$2")).Index
For V = FirstSh To SecondSh
'do code
Next
Set Regex = Nothing
Else
'code
End If
Cheers
Dave