Alphabetize Tab & Toc's Hyperlink

[Guest]

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 Before:=Worksheets(M)
End If
Else
If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then
Worksheets(N).Move Before:=Worksheets(M)
End If
End If
Next N
Next M

End Sub

 

[Gord Dibben]

The Sub SortWorksheets is woking for me.

How are you running it?


Gord Dibben MS Excel MVP

 

[Guest]

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]

Ben

The SortWorksheets is not event code and has to be run by some other method like
Tools>Macro>Macros>Run 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 Insert>Module.

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

 

[Guest]

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]

Ben

The SortWorksheet macro sorts the sheet tabs you see across the bottom of the
Window.

What do you want sorted?

After pasting the macro into Module1 did you go back to Excel window and
Tools>Macro>Macros, select the macro and "Run"?

What kind of summary page?

Your Worksheet_Activate code clears all cells and puts hyperlinks to the sheets.


Gord

 

[Guest]

Gord,

It works but then produced the following error:
"Run-TIme error '28'

Out of Stack Space"

I must have put the following in the wrong place becuase when I take it out
it runs with no errors:
">>>Do you want to re-sort the sheets every time your sheet event code runs?
Thanks for your help!!!

Ben
-------------------------------

 

[Gord Dibben]

Did you add just the one line "SortWorksheets"(no quotes) between Next and End
Sub in your WorkSheet_Activate code?

Private Sub Worksheet_Activate()
Dim anySheet As Worksheet
Dim myCounter As Long

' SortWorksheets

Cells.Clear ' remove previous content
' Range("B:B").Clear 'may be better
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

' SortWorksheets

End Sub

Since you haven't explained what the purpose of all this is I can't make any
comments, just guesses.

Might be you just want the list of Hyperlinks in Column B sorted?

In that case maybe sort the sheets first?

Put the SortWorksheets line just below Dim Counter as Long so sheets are sorted
before the hyperlinks are created? See above.

Have a look at David McRitchie's site for TOC

http://www.mvps.org/dmcritchie/excel/buildtoc.htm


Gord