xlf | xlfSortVisibleWSheetsFromList macro part 2


0. Sort list, and sort WS from list macros


This module extends xlfSortVisibleWSheetsFromList (part 1) with the sort event moved to the Option Buttons. See figure 1 for Controls animation.


xlf-sort-sheets-part2
Fig 1: xlfAnimatedWS - with Sheetlist, on-sheet Form controls - Button, Group Box, and Option Button sort

About the WS components:

  1. Defined name Sheetlist: is a WS dynamic range, with RefersTo: =OFFSET(WorkArea!$C$5,0,0,COUNTA(WorkArea!$C:$C)-1) A dynamic range anchored on cell $C$5, the first sheet name in the list
  2. Defined name SortAsc RefersTo: =WorkArea!$F$6. Located behind the on-sheet controls

About the on-sheet control components (ver. 2):

  1. GroupBox: Caption: "Sort Order"; Name: "Group Box 3";
  2. OptionButton: Caption: "Ascending"; GroupBox.Name: "Group Box 3"; Name: "Option Button 4". Ascending is first in the group, so it returns the value 1
  3. OptionButton: Caption: "Descending"; GroupBox.Name: "Group Box 3"; Name: "Option Button 5". Descending is second in the group, so it returns the value 2
  4. Button: Caption: "Sort WS from list"; OnAction: "'xlf-sort-sheets.xlsm'!Sheet8.xlfSortVisibleWSheetsFromList1"; Name: "Button 7"

xlf-sort-sheets-parts-1to2
Fig 2: Comparison - Sheetlist, on-sheet Form controls - with Sort Sheetlist operation (ver.1) assigned directly to the Option Buttons (ver. 2 - this version)

1. xlfSortVisibleWSheetsFromList - the VBA code


The amended section of code 1 is shown by the ▼ ▲ indicators


1.1 "Ascending" and "Descending" option buttons



Code 1: Sub SortAsc_Click and SortDesc_Click replaces the Sort Sheetlist button (ver. 1) 
Option Explicit
Const List As String = "SheetList"
' [Sheet8 (Code)] Module (WorkArea)
'============================
' By-Pass the control Cell Link
▼ START Sort List Group 

Private Sub SortAsc_Click()
    cmdSort_Click 1
End Sub

Private Sub SortDesc_Click()
    cmdSort_Click 2
End Sub
'============================
▲ END Sort List Group 
'============================
' Sort WS from list button
Private Sub cmdSort_Click(Sort As Integer)
Dim SortDir As Long
    If Sort = 1 Then SortDir = xlAscending Else SortDir = xlDescending
    Range(List).Sort _
        Key1:=Range(List).Range("A1"), _
        Order1:=SortDir, _
        Header:=xlNo
End Sub
'

1.2 "Sort WS from List" button



Code 2: Macro xlfSortVisibleWSheetsFromList1 for the Sort WS from List button 
'============================
' Sort WS from List
Private Sub xlfSortVisibleWSheetsFromList1()
' List values must be valid worksheet names
' Worksheet must be visible (visibility property)
Dim ASht As String
Dim i As Integer

    ASht = ActiveSheet.Name
    Application.ScreenUpdating = False

    With Range(List) ' a one column range
        For i = .Rows.Count To 1 Step -1
            Worksheets(.Cells(i, 1).Value).Move before:=Worksheets(1)
        Next i
    End With

    Worksheets(ASht).Activate
    Application.ScreenUpdating = True
End Sub
'