Sort worksheets based on list of worksheet names

[neekokeen]

Hi guys,

First off I'd like to thank all contributors to this site. I have a lot of your threads to be grateful for!

I have an Excel file, where 'modules' can be selected with a drop down:

Each of these modules have a worksheet with the same name.
I have a VBA code that will only show the worksheets for the Modules that are selected (in this range of C18:C27, the blue cells you see), and hides all worksheets that are not.
So If I select 'Module_FO10' in the first cell, and 'Module RS10' in another, it will unhide the tabs with these same names. As soon as I clear them from my list, they are hidden.
This works perfectly.

What I'd like to have now, is that the worksheets are sorted automatically according to the order of this list in C18:C27. So for example if I select Module_RO20 in C18, and Module_FO10 in C19, I'd like for the worksheet tab of Module_RO20 to be sorted before Module_FO10.

Ideally, it should reset the tab order back to original when the selections are cleared. This can probably be done based on a list I have in a hidden 'helpsheet' with the initial order of the tabs.

I have read a few threads on this, such as here and here, but I can't get it to work.
Thanks in advance for any feedback!

 

[Russell Hauf]

Hi - welcome to the board!

Sorry it has taken so long - this one looked fun and I wanted to try it, but I don't get as much time for fun stuff as I used to. Here's one way to do it.

I created a named range for your C18:C27 range (rngWkSheets - clever name, isn't it?). Then, using the Worksheet_Change event, I checked for when this range changes.

First, in a standard code module:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> HideAllSheetsButMain()<br>    <SPAN style="color:#00007F">Dim</SPAN> cl <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cl <SPAN style="color:#00007F">In</SPAN> Worksheets("Main").Range("C1:C10").Cells<br>        Worksheets(cl.Text).Visible = xlSheetVeryHidden<br>    <SPAN style="color:#00007F">Next</SPAN> cl<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

And in the Worksheet_Change event:

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> cl <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> wks <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rng = ThisWorkbook.Names("rngWkSheets").RefersToRange<br>    <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        HideAllSheetsButMain<br>        <SPAN style="color:#00007F">Set</SPAN> wks = ThisWorkbook.Worksheets("Main")<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cl <SPAN style="color:#00007F">In</SPAN> rng<br>            <SPAN style="color:#00007F">If</SPAN> cl.Text <> "" <SPAN style="color:#00007F">Then</SPAN><br>                Worksheets(cl.Text).Visible = xlSheetVisible<br>                Worksheets(cl.Text).Move After:=wks<br>                <SPAN style="color:#00007F">Set</SPAN> wks = Worksheets(cl.Text)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> cl<br>        Me.Select<br>        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Seems to work for me. Please let us know if you have issues or questions - happy to discuss.

Have fun,

 

[Russell Hauf]

Oh, and by they way, I named the sheet with the Module names on it, "Main."

And I have my Data Validation range (containing the sheet names that start with "Module") in cells C1:C10. Obviously, you could do the hiding of all the sheets in many ways...this was quick n dirty....

 

[neekokeen]

Thanks so much for replying.
Unfortunately I cannot get it to work.
I'm having trouble understanding your code, so I have no clue where to begin in altering it..
Would you mind taking a look at my file? I put a stripped version of it on my dropbox HERE
(please note I'm rather new to vba, so my codes might be very chaotic..)


In this example, the tab for Module_ZW10 should come before Module_FO10, as it is selected first:

This is my current code for showing and hiding the module tabs:

Private Sub Worksheet_Calculate()If ActiveWorkbook.Name <> ThisWorkbook.Name Then End
Dim sh As Worksheet
Set sh = Worksheets("Hulptabel")
With Sheets("Module_FO10")
If sh.Range("B19").Value = 1 Then
.Visible = xlSheetVisible
ElseIf sh.Range("B19").Value = 0 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Module_TF10")
If sh.Range("B20").Value = 1 Then
.Visible = xlSheetVisible
ElseIf sh.Range("B20").Value = 0 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Module_RS10")
If sh.Range("B21").Value = 1 Then
.Visible = xlSheetVisible
ElseIf sh.Range("B21").Value = 0 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Module_PB10")
If sh.Range("B22").Value = 1 Then
.Visible = xlSheetVisible
ElseIf sh.Range("B22").Value = 0 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Module_ZW10")
If sh.Range("B23").Value = 1 Then
.Visible = xlSheetVisible
ElseIf sh.Range("B23").Value = 0 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Module_ZW05")
If sh.Range("B24").Value = 1 Then
.Visible = xlSheetVisible
ElseIf sh.Range("B24").Value = 0 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Module_RO20")
If sh.Range("B25").Value = 1 Then
.Visible = xlSheetVisible
ElseIf sh.Range("B25").Value = 0 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Module_RO15")
If sh.Range("B26").Value = 1 Then
.Visible = xlSheetVisible
ElseIf sh.Range("B26").Value = 0 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Module_GE20")
If sh.Range("B27").Value = 1 Then
.Visible = xlSheetVisible
ElseIf sh.Range("B27").Value = 0 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Module_GE15")
If sh.Range("B28").Value = 1 Then
.Visible = xlSheetVisible
ElseIf sh.Range("B28").Value = 0 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Module_GE10")
If sh.Range("B29").Value = 1 Then
.Visible = xlSheetVisible
ElseIf sh.Range("B29").Value = 0 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Module_GE05")
If sh.Range("B30").Value = 1 Then
.Visible = xlSheetVisible
ElseIf sh.Range("B30").Value = 0 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Module_GR10")
If sh.Range("B31").Value = 1 Then
.Visible = xlSheetVisible
ElseIf sh.Range("B31").Value = 0 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Module_GR05")
If sh.Range("B32").Value = 1 Then
.Visible = xlSheetVisible
ElseIf sh.Range("B32").Value = 0 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("POS")
If sh.Range("B33").Value = 1 Then
.Visible = xlSheetVisible
ElseIf sh.Range("B33").Value = 0 Then
.Visible = xlSheetHidden
End If
End With
End Sub

It uses a workaround with the 'Hulptabel' sheet, which I hide.
Perhaps not the most efficient way to do this, but it works great.

 

[Russell Hauf]

Hi - sorry about that. Please try this (I realize that some things may be different in your version of Excel - which I don't think is in English - and am hoping you can figure out those differences):

• Name the range: C18:C27 in your Offerte worksheet: "Module_Sort" (or whatever you want, as long as you change "Module_Sort" in the code below to the same name). I see you already have a named range of "Modules," which I am using in the code below as well.
• Delete the code you have in Worksheet_Calculate (you'll no longer need it)
• In the VBE (Visual Basic Editor), click on your workbook/project, then go to the Insert menu, and select "Module"
• Double-Click on the module that was just created (will be something like "Module1"), and paste in the following code:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> HideAllSheetsButMain()<br>    <SPAN style="color:#00007F">Dim</SPAN> cl <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cl <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Names("Modules").RefersToRange.Cells<br>        Worksheets(cl.Text).Visible = xlSheetVeryHidden<br>    <SPAN style="color:#00007F">Next</SPAN> cl<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

• Then double-click on your "Blad4 (Offerte)" worksheet (still in the VBE), and paste in the following code:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> cl <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> wks <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rng = ThisWorkbook.Names("Module_Sort").RefersToRange<br>    <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        HideAllSheetsButMain<br>        <SPAN style="color:#00007F">Set</SPAN> wks = Me  <SPAN style="color:#007F00">'ThisWorkbook.Worksheets("Main")</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cl <SPAN style="color:#00007F">In</SPAN> rng<br>            <SPAN style="color:#00007F">If</SPAN> cl.Text <> "" <SPAN style="color:#00007F">Then</SPAN><br>                Worksheets(cl.Text).Visible = xlSheetVisible<br>                Worksheets(cl.Text).Move After:=wks<br>                <SPAN style="color:#00007F">Set</SPAN> wks = Worksheets(cl.Text)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> cl<br>        Me.Select<br>        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>     <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

Then I suggest deleting all of your module names in C19:C27, just to get a clean start (you can keep C18). This should then work as you add/remove modules from your list in C18:C27.

Hope this helps - let me know if not. And have fun!

 

[Russell Hauf]

I realize that I should have named the separate function something like, "HideAllModuleSheets," but hopefully you get the gist of it. Also, disregard (delete) the commented code that refers to ThisWorkbook.Worksheets("Main").