Sorting Sheet Tabnames by group type, number and tab colour

Topic

New Reply

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
.

Reply | Quote

Replies

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
.

Reply | Quote

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:

[COLOR=”#0000FF”]Public ShtName As String[/COLOR]
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

[COLOR=”#008000″]’******rest of code******[/COLOR]

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)
[COLOR=”#0000FF”]If Range(“A3”)  ShtName Then[/COLOR]
    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
[COLOR=”#0000FF”]End If[/COLOR]
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

Reply | Quote

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
.

Reply | Quote

Zeddy & Maud,

:thewave:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote