AngleseyExcel
New Member
- Joined
- Feb 4, 2021
- Messages
- 38
- Office Version
- 365
- 2016
- Platform
- Windows
I need help changing a piece of code that @JoeMo came up with on this forum.
i'd like it if the above code didn't give me hyperlinks, and only listed the 4 worksheets that come after the 'SA Overtime Sheet' worksheet as you can see in the attached image, nothing else.
Is it possible ?
Any help would be greatly appreciated.
TIA
VBA Code:
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim n As Integer
Dim calcState As Long, scrUpdateState As Long
calcState = Application.Calculation
Application.Calculation = xlCalculationManual
scrUpdateState = Application.ScreenUpdating
Application.ScreenUpdating = False
n = 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
n = n + 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(n, 1), Address:="", _
SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet
Application.Calculation = calcState
Application.ScreenUpdating = scrUpdateState
End Sub
i'd like it if the above code didn't give me hyperlinks, and only listed the 4 worksheets that come after the 'SA Overtime Sheet' worksheet as you can see in the attached image, nothing else.
Is it possible ?
Any help would be greatly appreciated.
TIA
Attachments
Last edited by a moderator: