VBA Personal Macro Contents Page

[toffee_girl]

Hi Everyone,

Hoping somebody can help me before I throw my lap top through the window...!

I attempted to write a macro which was supposed to add a contents page with links to all other worksheets and then on each of those worksheets insert a shape with a link back to the contents page. After about 100 attempts the macro worked fine - until I tried to save it as a personal macro. Now when I run it the initial part works and it creates the contents page and adds the shapes but when I try to click back on the shape I get an error message to say "Cannot run the Macro 'Module2.ContentsSelect'. The Macro may not be available in this workbook or all macros may be disabled.

Any help would be much appreciated !

 Sub Contents()

Dim wbBook As Workbook
Dim wsActive As Worksheet
Dim wsSheet As Worksheet


Dim lnRow As Long
Dim lnPages As Long
Dim lnCount As Long


Set wbBook = ActiveWorkbook


With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With


On Error Resume Next


With wbBook
    .Worksheets("Contents").Delete
    .Worksheets.Add Before:=.Worksheets(1)
End With
On Error GoTo 0


Set wsActive = wbBook.ActiveSheet
With wsActive
    .Name = "Contents"
    With .Range("B2")
        .Value = VBA.Array("Table of Contents")
        .Font.Bold = True
    End With
End With


lnRow = 3
lnCount = 2


For Each wsSheet In wbBook.Worksheets
    If wsSheet.Name <> wsActive.Name Then
        wsSheet.Activate
        With wsActive
            .Hyperlinks.Add .Cells(lnRow, 2), "", _
            SubAddress:="'" & wsSheet.Name & "'!A1", _
            TextToDisplay:=wsSheet.Name
            
        End With
        lnRow = lnRow + 1
        lnCount = lnCount + 1
    End If
Next wsSheet


wsActive.Activate
wsActive.Columns("B").EntireColumn.AutoFit


ActiveWindow.DisplayGridlines = False


Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets


Set s = ws.Shapes.AddShape(51, 300, 10, 100, 30)
With s
    .Fill.ForeColor.RGB = RGB(204, 193, 218)
    .TextFrame.Characters.Text = "Back to Contents"
    .OnAction = "Module2.ContentsSelect"
        
    
End With


Next


With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
End With
End Sub

Sub ContentsSelect()
        
        Worksheets("Contents").Activate
    
        Range("B2").Select
    
End Sub

 

[shg]

Welcome to the board.

Where is the macro ContentsSelect? If it's in Personal, the line should be

.OnAction = "personal.xls!ContentsSelect"

or

.OnAction = "personal.xls[COLOR="#FF0000"]m[/COLOR]!ContentsSelect"

or

.OnAction = "personal.xls[COLOR="#FF0000"]b[/COLOR]!ContentsSelect"

..., depending.

That said, why not put the code in the workbook of interest, so it's not dependent on Personal?

 

[toffee_girl]

This worked perfectly, thank-you very much!! The reason its a personal macro is because I get a lot of system generated spreadsheets with multiple pages and I wanted to be able to add a contents page to each one to make improve ease of navigation. Have I misunderstood the use of a personal macro?

Thanks again!

 

[shg]

You're welcome.