Visual Basic code stopped working

[squirrellydw]

This was working fine for years but now it gives an error, any idea what is causing it? I think it has something to do with the Address but not sure. Error code is "Run-time error 1004 Application-defined or object-defined error"

Private Sub Worksheet_Activate()
  Dim wks           As Worksheet
  Dim iRow          As Long
 
  With Me.Range("A1")
    .EntireColumn.ClearContents
    .Value = "Index"
    .Name = "Index"
  End With

  iRow = 1
 
  For Each wks In Me.Parent.Worksheets
    If Not wks Is Me And wks.Visible = xlSheetVisible Then
      iRow = iRow + 1
      With wks
        .Range("A1").Name = "Start_" & .Index
        .Hyperlinks.Add Anchor:=.Range("A1"), _
                        Address:="", _
                        SubAddress:="Index", _
                        TextToDisplay:="Back to Employee List"
      End With
      Me.Hyperlinks.Add Anchor:=Me.Cells(iRow, 1), _
                        Address:="", _
                        SubAddress:="Start_" & wks.Index, _
                        TextToDisplay:=wks.Name
    End If
  Next wks
End Sub

 

[zero269]

This was working fine for years but now it gives an error, any idea what is causing it? I think it has something to do with the Address but not sure. Error code is "Run-time error 1004 Application-defined or object-defined error"

Private Sub Worksheet_Activate()
  Dim wks           As Worksheet
  Dim iRow          As Long
 
  With Me.Range("A1")
    .EntireColumn.ClearContents
    .Value = "Index"
    .Name = "Index"
  End With

  iRow = 1
 
  For Each wks In Me.Parent.Worksheets
    If Not wks Is Me And wks.Visible = xlSheetVisible Then
      iRow = iRow + 1
      With wks
        .Range("A1").Name = "Start_" & .Index
        .Hyperlinks.Add Anchor:=.Range("A1"), _
                        Address:="", _
                        SubAddress:="Index", _
                        TextToDisplay:="Back to Employee List"
      End With
      Me.Hyperlinks.Add Anchor:=Me.Cells(iRow, 1), _
                        Address:="", _
                        SubAddress:="Start_" & wks.Index, _
                        TextToDisplay:=wks.Name
    End If
  Next wks
End Sub

I tested your code in a Standard Module and got an error.
I had to look it up what Me was in your code, and from what I found, this only works when the code in the Worksheet Code and not a Standard Module.

What is "Me.Range"?

Dear gurus, probably a simple question: What does "Me.Range" mean? More specifically, I don't understand the second line of this piece of VBA code: Dim LP as Long LP = Me.Range("r4").Value Thank you!

www.mrexcel.com

So, I created a new worksheet and placed the code in the Worksheet Code (right-click on the Sheet name > View Code) and past it there.
Now it's working for me.
Pretty cool. It created a table of contents for all visibile worksheets with hyperlinks.

 

[squirrellydw]

Still not working for me and that's how I originally did it. Any other ideas?

 

[squirrellydw]

attached is a screenshot of the code incase I'm doing something wrong

 

[dermie_72]

where is the code located in VB?

The yellow or blue?

 

[squirrellydw]

Does this help show it

 

[zero269]

attached is a screenshot of the code incase I'm doing something wrong

I should note that I learned something new today...

OK, so I took a look into why the Worksheet_Activate isn't triggering in your workbook.

I think you have Worksheet Events off.

Troubleshooting Steps:

1. I created a new workbook and added your code to Sheet1
2. Added a few Sheets
3. Selected Sheet1 and the code ran successfully
4. Disabled Worksheet Events
5. Added a new sheet
6. Selected Sheet1 and the code did not run
7. Enabled Worksheet Events
8. Selected Sheet1 and the code ran successfully

I would first check the staus using the Immediate Window: ?Application.EnableEvents

If it returns False, then enable it using:

Sub DisableEvents()
    Application.EnableEvents = True
End Sub

 

[squirrellydw]

I should note that I learned something new today...

OK, so I took a look into why the Worksheet_Activate isn't triggering in your workbook.

I think you have Worksheet Events off.

Troubleshooting Steps:

1. I created a new workbook and added your code to Sheet1
2. Added a few Sheets
3. Selected Sheet1 and the code ran successfully
4. Disabled Worksheet Events
5. Added a new sheet
6. Selected Sheet1 and the code did not run
7. Enabled Worksheet Events
8. Selected Sheet1 and the code ran successfully

I would first check the staus using the Immediate Window: ?Application.EnableEvents

View attachment 121753​

If it returns False, then enable it using:

Sub DisableEvents()
    Application.EnableEvents = True
End Sub

View attachment 121754

it came back TRUE, any other ideas?

 

[zero269]

it came back TRUE, any other ideas?

Check ScreenUpdating, too... ?Application.ScreenUpdating

Application.ScreenUpdating = True

Then be sure to select a different worksheet first, add a new Sheet, then select the worksheet that triggers the Worksheet_Activate event again to see if that new Sheet was added.

 

[MARK858]

@squirrellydw is the sheet protected?