Example Sorting Sheets in a Workbook

Let us add a new utility to our SRXUtils application. If you work with workbooks that contain many sheets (worksheets and chartsheets), then you may want to sort the sheets in alphabetical order.

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 a b _c_i p e f _b__h

Ullkty ¡J-J^cNd-.I'rp: rrncidura tiWotbui* Mwnjttim StliHinuNan anVflfflfcfflJ DhChi^Wnu

-, "livl*.l"f \t tit. flt-.Tl-.n-H Ih-.-.-.Y--,.-riu —I T< ! "I I

: F I R.itil, F'■ -i ■ ■ =TViU 4='.: r- :■-_■! i-i' ,i? -= r "= J

^ p -i"" i m r.-iji il p-irv.fflh^ fikii* tbue ra r

5 -■Tifrw??*! k.-Ltitj k-B—J-r.'-- -NT I.- ^^inh ' '- I

- T-^TII-.J. p.-.t.f- 71. "= " "= r

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 SortAllSheets 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)

' Fill array with worksheet names For i = 1 To cSheets sSheets(i) = wb.Sheets(i).Name Next

' Create new sheet and put names in first column Set ws = wb.Worksheets.Add

For i = 1 To cSheets ws.Cells(i, 1).Value = sSheets(i) Next

' Sort column ws.Columns(1).Sort Key1:=ws.Columns(1), Order1:=xlAscending

' Refill array

For i = 1 To cSheets sSheets(i) = ws.Cells(i, 1).Value Next

' Delete extraneous sheet Application.DisplayAlerts = False ws.Delete

Application.DisplayAlerts = True

' Reorder sheets by moving each one to the end For i = 1 To cSheets wb.Sheets(sSheets(i)).Move After:=wb.Sheets(cSheets) Next End Sub

Once the code is inserted, you can save the SRXUtils.xls workbook as an add-in. Don't forget to unload the add-in first, or Excel will complain.

0 0

Post a comment