Consulting

Results 1 to 6 of 6

Thread: Sleeper: Pesky RefEdit box

  1. #1
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location

    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?

    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

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Would something like this work for you?


    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.

  3. #3
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    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

  4. #4
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    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?

  5. #5
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    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
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  6. #6
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •