Indexing in Excel using VBA

Question

The below code is used in My workbook for Indexing. Problem is this code is overwriting the cells in A1 with "Back to Index". How can I modify this code so it does not overwrite the current value A1 cell in each sheet and a Hyperlink in A1 cell with current value retained is created back to the Index page? TIA

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long

l = 1

    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
    End With


    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name Then
            l = l + 1
                With wSheet
                    .Range("A1").Name = "Start_" & wSheet.Index
                    .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
                    SubAddress:="Index", TextToDisplay:="Back to Index"
                End With

                Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
                SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
        End If
    Next wSheet

End Sub

user4039065 · Accepted Answer · 2017-09-21 18:05:50Z

1

Seems pretty straightforward to me but perhaps I did not understand the problem.

    With wSheet
        .Range("A1").Name = "Start_" & .Index
        .Hyperlinks.Add Anchor:=.Range("A1"), Address:=vbNullstring, _
        SubAddress:="Index", TextToDisplay:=.Range("A1").Text
    End With

answered Sep 21, 2017 at 18:05

user4039065

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Indexing in Excel using VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related