Results 1 to 5 of 5
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Sorting Sheet Tabnames by group type, number and tab colour

    Hi all

    I've been having a look at automatic sorting of sheet tabnames when you make a change to a sheet tabname.
    (This was triggered after looking at some of Ferenc's files)

    As we know, there isn't currently a 'changed-sheetname event'.

    So we have to look at other ways of doing this.

    I have attached an example file to show my progress so far.
    If testing and playing with this, don't have your own files open!

    When sorting by sheet tabnames by number, all non-numeric characters in sheet tabnames are ignored.
    1 is followed by 2 (rather than 'text-sort-order' 1, 11, 114 etc etc)

    When sorting by type, sheet tabs are sorted in numeric order by group character + , # , @ and ! in the sheet tab names

    If you edit a sheet tabname by adding or removing a group character +, #, @ or !, then when you move the cellpointer on that particular sheet, this will trigger the automatic sorting of sheets by type.

    The routines in this workbook use cells [a1] and [a2] on each sheet. Other cells could be used by changing the vba code in the Workbook Open event

    I would be interested in your comments, views, and suggestions for improvement.

    zeddy
    •Tropical Wet Leisure Guard
    .
    Attached Files Attached Files

  2. The Following User Says Thank You to zeddy For This Useful Post:

    Maudibe (2015-07-20)

  3. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    If you edit a sheet tabname by adding or removing a group character +, #, @ or !, then when you move the cellpointer on that particular sheet, this will trigger the automatic sorting of sheets by type.
    ..what this means, for example, is if you change the tabname [5] to [5#], and then move the cellpointer on that sheet, it will be moved automatically and placed in sequential order with the other sheet tabnames 'ending in #'. Similarly, if you rename sheet [11#] to [11], it will be moved and placed in numeric sequence with others in that group. The sheet tab colours will also be adjusted automatically.
    (The group tab colours are defined in the vba code and can be adjusted as required)

    zeddy
    •Organic Hoist Specialist
    .

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Hi Zeddy,

    You have obviously put some thought into this. Very nice code. I have detected one issue.

    Scenario:
    If a user renames the sheet but hits enter instead of clicking on the sheet, the Workbook_SheetSelectionChange will not fire and the sort for the worksheets does not occur until click somewhere on the sheet. But, what if the user clicks on another tab instead and starts working on that sheet? The sort will not occur until the user goes back to the original sheet and clicks on a cell to elicit the Workbook_SheetSelectionChange event. Here is my suggestion

    Resolution:
    Since you have reserved cells on each worksheet, have one more on each sheet, say cell A3, with the formula:

    =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("filename",A1)))

    This will return the sheet's name. Then in your modSort standard module, declare a global string variable at the top (blue):

    modSort Standard Module:
    Code:
    Public ShtName As String
    Sub sortSheetsByType()
    
    zReturnTo = ActiveSheet.Name
    
    Set zSht = Sheets.Add               'create and use temporary sheet
    r = 1                               'row counter
    For Each z In ThisWorkbook.Sheets   'loop through all worksheets
    zTab = z.Name                       'sheet tabname
    z.Tab.Color = xlAutomatic           'set initial tab colour to none
    
    'COLOUR SHEET TABS ACCORDING TO GROUP..
    If IsNumeric(zTab) Then z.Tab.Color = rgbLawnGreen
    If InStr(zTab, "#") Then z.Tab.Color = rgbAqua
    If InStr(zTab, "+") Then z.Tab.Color = rgbRed
    If InStr(zTab, "!") Then z.Tab.Color = rgbGold
    If InStr(zTab, "@") Then z.Tab.Color = rgbBlack
    
    '******rest of code******
    Next, add the following code to the Workbook_SheetActivate event of the ThisWorkbook module
    Code:
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
        ShtName = Sh.Name
    End Sub
    Lastly, move your code from the Workbook_SheetSelectionChange to the Workbook_SheetCalculate of the ThisWorkbook module and add the lines in blue
    Code:
    Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    If Range("A3") <> ShtName Then
        zFormula1 = UCase(Sh.[a2].Formula)
        zFormula2 = UCase("" & Sh.[a1])
        If zFormula1 = zFormula2 Then Exit Sub
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        sortSheetsByType
        Sh.Select
        [a1] = "'='" & Sh.Name & "'!a1"
        Application.EnableEvents = True
    End If
    End Sub
    How it works:
    When a sheet is activated, the global variable ShtName is given the sheet's current name (Workbook_SheetActivate). The A3 cells (formula) monitors the sheet names of the sheet they reside and if the sheet name is changed, so is its value. The Workbook_SheetCalculate picks up the A3 change and compares it to its previous name stored in ShtName. If they are not the same, your sort routing is initiated. This way, it doesn't matter what the user's next action will be. It is using the calculation event as the mechanism to create a pseudo_sheet name change event.

    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2015-07-20 at 00:45.

  5. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    motivated (2015-07-22),zeddy (2015-07-20)

  6. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy & Maud,

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    Thanks for your input.
    In my version, I was using the fact that if you put the sheetname in a formula reference, changing the sheet name is supposed to trigger the calculate event. For example, in a new worksheet named [Fred], if you enter in cell [A2] the formula =Fred!A1
    ..then, when you rename the sheet to say, [Brian] the cell will now show this.
    The calculate event is supposed to fire.
    ..but it doesn't seem to be consistent!!!
    ..I am still working on this.

    zeddy
    •Wheel Spokesman
    .

Posting Permissions

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