xlf | xlfSortVisibleWSheetsFromList macro


0. Sort list, and sort WS from list macros


This module features on-sheet Form controls with associated List and WS sort procedures. See figure 1 for Controls animation.


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

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:

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

1. xlfSortVisibleWSheetsFromList - the VBA code


1.1 "Sort Sheetlist" button



Code 1: Sub cmdSort_Click for the Sort Sheetlist button 
Option Explicit
Const List As String = "SheetList"
' The name SheetList refersto: =OFFSET(WorkArea!$C$5,0,0,COUNTA(WorkArea!$C:$C)-1)

' {Sheet8 (Code)] Module (WS: WorkArea)

'============================
' Sort List
Private Sub cmdSort_Click()
Dim SortDir As Long

    If Range("SortAsc") = 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
'