Microsoft Office Tutorials and References
In Depth Information
Figure 17-8. Augmenting the DataSheet worksheet
The basis for the code to order the sheets is the Move method of the Worksheet and Chart objects.
Its syntax is:
SheetsObject .Move( Before , After )
Of course, to use this method effectively, we need a sorted list of sheet names.
The first step is to augment the DataSheet worksheet for SRXUtils by adding a new row for the
new utility, as shown in Figure 17-8 . (The order of the rows in this DataSheet is based on the
order in which we want the items to appear in the custom menu.)
Figure 17-8. Augmenting the DataSheet worksheet
Next, we insert a new code module called basSortSheets , which will contain the code to
implement this utility.
We shall include two procedures in basSortSheets . The first procedure verifies that the user
really wants to sort the sheets. If so, it calls the second procedure, which does the work. The first
procedure is shown in Example 17-1 . It displays the dialog box shown in Figure 17-8 .
Example 17-1. The SortSheets Procedure
Sub SortSheets()
If MsgBox("Sort the sheets in this workbook?", _
vbOKCancel + vbQuestion, "Sort Sheets") = vbOK Then
End If
End Sub
The action takes place in the procedure shown in Example 17-2 . The procedure first collects the
sheet names in an array, then places the array in a new worksheet. It then uses the Sort method
(applied to a Range object, discussed in Chapter 19 ) to sort the names. Then, it refills the array
and finally, reorders the sheets using the Move method.
Example 17-2. The SortAllSheets Procedure
Sub SortAllSheets()
' Sort worksheets
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim cSheets As Integer
Dim sSheets() As String
Dim i As Integer
Set wb = ActiveWorkbook
' Get true dimension for array
cSheets = wb.Sheets.Count
ReDim sSheets(1 To cSheets)
Search JabSto ::

Custom Search