Gord,

I followed the instructions and it created a "Modules" Folder on the same
level as "Microsoft Excel Objects" and then a "Module1" Under the "Modules"
Folder.

It still doesn't work. I appreciate your patience in helping!

One other Question that will go along with the TOC is that I am using the
TOC as a summary page of the other worksheets Data also. When we get the
sort working how do we get the data to sort with the Link.

Ben

"Gord Dibben" wrote:

Ben

The SortWorksheets is not event code and has to be run by some other method like
ToolsMacroMacrosRun or from a button.

Do not store it in the sheet module with the event code.

Store it in a general module.

Alt + F11 to open VBEditor. CTRL + r to open Project Explorer.

Select your workbook/project and InsertModule.

Paste the SortWorksheets macro into that general module.

Do you want to re-sort the sheets every time your sheet event code runs?

If so, add the line SortWorksheets to your worksheet_activate code

Next
SortWorkSheets 'add this line
End Sub


Gord

On Wed, 24 Jan 2007 15:10:00 -0800, Ben Dummar
wrote:

I am not sure what you mean by "running it". I have the code in the "view
code" section of the first worksheet Tab in the workbook. I have copied and
pasted it to another workbook to test it and the alphabetizing is not working
there either.

Ben

"Gord Dibben" wrote:

The Sub SortWorksheets is woking for me.

How are you running it?


Gord Dibben MS Excel MVP


On Wed, 24 Jan 2007 14:04:01 -0800, Ben Dummar
wrote:

I am trying to create a TOC with links in the first sheet with the sheets
from 4 on being alphabetized. The TOC with links is working but not the
alphabetizing fo tabs.

What am I missing?

Ben

Code used below------
Private Sub Worksheet_Activate()
Dim anySheet As Worksheet
Dim myCounter As Long

Cells.Clear ' remove previous content
myCounter = 0
For Each anySheet In Worksheets
If anySheet.Name < ActiveSheet.Name Then
Range("B2").Offset(myCounter, 0) = anySheet.Name
'this adds hyperlink remove if you don't want it
ActiveSheet.Hyperlinks.Add anchor:=Range("B2").Offset(myCounter, 0), _
Address:="", SubAddress:="'" & anySheet.Name & "'!A1"
'update the offset counter, keep this
myCounter = myCounter + 1
End If
Next
End Sub

Sub SortWorksheets()

Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean

SortDescending = False

If ActiveWindow.SelectedSheets.Count = 1 Then
FirstWSToSort = 4
LastWSToSort = Worksheets.Count
Else
With ActiveWindow.SelectedSheets
For N = 2 To .Count
If .Item(N - 1).Index < .Item(N).Index - 1 Then
MsgBox "You cannot sort non-adjacent sheets"
Exit Sub
End If
Next N
FirstWSToSort = .Item(1).Index
LastWSToSort = .Item(.Count).Index
End With
End If

For M = FirstWSToSort To LastWSToSort
For N = M To LastWSToSort
If SortDescending = True Then
If UCase(Worksheets(N).Name) UCase(Worksheets(M).Name) Then
Worksheets(N).Move Befo=Worksheets(M)
End If
Else
If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then
Worksheets(N).Move Befo=Worksheets(M)
End If
End If
Next N
Next M

End Sub