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.
About the WS components:
- 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 - Defined name SortAsc RefersTo:
=WorkArea!$F$6
. Located behind the on-sheet controls
About the on-sheet control components:
- GroupBox: Caption: "Sort Order"; Name: "Group Box 3";
- 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
- 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
- Button: Caption: "Sort Sheetlist"; OnAction: "'xlf-sort-sheets.xlsm'!Sheet8.cmdSort_Click"; Name: "Button 1"
- 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 '
- Download: xlf-sort-sheets.xlsm [30 KB ]
- Development platform: Office 365 ProPlus - Excel 2016 MSO (16.0...) 64 bit
- Published: 20th May 2018
- Revised: Friday 24th of February 2023 - 02:38 PM, Pacific Time (PT)