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

 

[squirrellydw]

@squirrellydw is the sheet protected?

parts of it are but it worked fine before. I will look try these suggestions tomorrow

 

[MARK858]

You'll get that error if any cell is protected and you try amending it (including the ClearContents).
Turn off your sheet protection at the start of the code and back on again at the end

Private Sub Worksheet_Activate()
  Dim wks           As Worksheet
  Dim iRow          As Long
  
  Me.Unprotect "your password here"
 
  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
  
  Me.Protect "Your password here"
  
End Sub

 

[squirrellydw]

still getting the same error, when I hit the debug button this part shows up highlighted in red

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

 

[squirrellydw]

Also not sure it matters but this is all the code on the sheet, still getting the same error, when I hit the debug button this part shows up highlighted in red

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


Sub Sort_Active_Book()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user as which direction they wish to
' sort the worksheets.
'
   iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
     & "Clicking No will sort in Descending Order", _
     vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
   For i = 1 To Sheets.Count
      For j = 1 To Sheets.Count - 1
'
' If the answer is Yes, then sort in ascending order.
'
         If iAnswer = vbYes Then
            If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
               Sheets(j).Move After:=Sheets(j + 1)
            End If
'
' If the answer is No, then sort in descending order.
'
         ElseIf iAnswer = vbNo Then
            If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
               Sheets(j).Move After:=Sheets(j + 1)
            End If
         End If
      Next j
   Next i
End Sub

 

[MARK858]

Also not sure it matters but this is all the code on the sheet, still getting the same error, when I hit the debug button this part shows up highlighted in red

I get no error running the code you posted in post 14 on an unprotected sheet

 

[squirrellydw]

I get no error running the code you posted in post 14 on an unprotected sheet

Any idea why it worked before when the sheet was protected?

 

[MARK858]

No I can only judge by what I see, my only guess is the cells that are locked at the time weren't affected by your data being added or the clear contents but that is only a guess.

You'll also have to be careful if the other sheets are protected as you are adding the hyperlink "Back to Employee List" to A1 on those sheets, which is the part of the code you colored red and so you probably need to unprotect and re-protect them as well.

All the sheets when I ran the code were unprotected.

 

[MARK858]

After doing some random testing I get the result you got in post 14 if any of the other sheets is protected and so you do need to unprotect/protect them in the code as well if any have protection set

 

[squirrellydw]

After doing some random testing I get the result you got in post 14 if any of the other sheets is protected and so you do need to unprotect/protect them in the code as well if any have protection set

ok, so how do I protect the other sheets with code, I have about 60 other sheets? Thanks for helping

 

[MARK858]

Is the password the same in all the other sheets?