Microsoft Office Tutorials and References
In Depth Information
A Realistic Example That Uses Sub Procedures
After I made all these corrections, the SortSheets procedure looked like this:
Option Explicit
Sub SortSheets()
‘ This routine sorts the sheets of the
‘ active workbook in ascending order.
‘ Use Ctrl+Shift+S to execute
Dim SheetNames() As String
Dim i As Long
Dim SheetCount As Long
Dim OldActiveSheet As Object
If ActiveWorkbook Is Nothing Then Exit Sub ‘ No active workbook
SheetCount = ActiveWorkbook.Sheets.Count
‘ Check for protected workbook structure
If ActiveWorkbook.ProtectStructure Then
MsgBox ActiveWorkbook.Name & “ is protected.”, _
vbCritical, “Cannot Sort Sheets.”
Exit Sub
End If
‘ Make user verify
If MsgBox(“Sort the sheets in the active workbook?”, _
vbQuestion + vbYesNo) <> vbYes Then Exit Sub
‘ Disable Ctrl+Break
Application.EnableCancelKey = xlDisabled
‘ Get the number of sheets
SheetCount = ActiveWorkbook.Sheets.Count
‘ Redimension the array
ReDim SheetNames(1 To SheetCount)
‘ Store a reference to the active sheet
Set OldActiveSheet = ActiveSheet
‘ Fill array with sheet names
For i = 1 To SheetCount
SheetNames(i) = ActiveWorkbook.Sheets(i).Name
Next i
‘ Sort the array in ascending order
Call BubbleSort(SheetNames)
‘ Turn off screen updating
Application.ScreenUpdating = False
 
Search JabSto ::




Custom Search