Sleeper: Pesky RefEdit box

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?

Code:

---

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.

Code:

---

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

Would something like this work for you?

Code:

---

Option Explicit
 
Sub MakeRefString()
Dim WS As Worksheet
Dim RngSelection As Range
Dim SelectedSheets() As Worksheet
Dim n As Long
ReDim SelectedSheets(1 To ActiveWindow.SelectedSheets.Count)
For Each WS In ActiveWindow.SelectedSheets
n = n + 1
Set SelectedSheets(n) = WS
Next
Set RngSelection = Selection
End Sub

---

This way you have the cell range, and an array of sheets to use in the rest of your code.

Hi DRJ,

Thats a nicer and simpler method than I'm using to return the sheets although I'm using the RefString in the RefEdit box on startup (forgot to mention that) to let the user see the individual sheets that will be formated. I think I'll combine the two approaches


For (2) I think I'm stuck with parsing the RefEdit control address if the user grabs a range

Cheers

Dave

Has anyone else tried to use a seemingly simple multi sheet range from a RefEdit box?

Is there a better way than my parsed string?

Dave,

I?ve been on this issue from time to time and I finally decided to what to post here :)

First of all, I total agree that the RefEdit-control is a poor and weak control. We can't use it with modeless forms (unless we want Excel to crash) and we need to consider if we should allow or not the Ctrl-Tab action and we need also to check the input before process it.

From what I can see and based on my experience You got the following options here in addition to the present one in use:

- Add additional RefEdit-controls to give the end-users the "built-in" feature or
- use an InputBox that loops until the user hit the cancel-button.
- or add a child-form that help the end-users to collect several ranges.

BTW, I have not yet find any third-part control that can replace this control...

B^2

Pesky RefEdit follow up see, http://www.vbaexpress.com/forum/showthread.php?t=1746