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