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]

Is the password the same in all the other sheets?

yes all the same

 

[Artik]

The original code for the Worksheet_Activate event could work with a protected worksheet, provided that some earlier procedure such as Worbook_Open protected the worksheet using the UserInterfaceOnly parameter.

Artik

 

[squirrellydw]

The original code for the Worksheet_Activate event could work with a protected worksheet, provided that some earlier procedure such as Worbook_Open protected the worksheet using the UserInterfaceOnly parameter.

Artik

Sorry, I have no idea what you mean

 

[MARK858]

Private Sub Worksheet_Activate()
  Dim wks           As Worksheet
  Dim iRow          As Long
 
 Me.Unprotect "Your password for the sheet with the code"
 
  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
 
    wks.Unprotect "your password for the other sheets"
 
      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
    
    wks.Protect "your password for the other sheets" 
    End If
 
   
 
  Next wks
 
  Me.Protect "Your password for the sheet with the code"
End Sub

You could also put the code for the sheets within the With statement but being honest you won't notice the difference.

As @Artik has stated you can use UserInterfaceOnly if you used code to set the passwords and had this parameter set at the time, you can't use it if you set your passwords manually or after you have set the password.
As you are unaware what UserInterfaceOnly is then this is unlikely, as you need to add it manually

We could amend the code above so it adds the parameter so it uses UserInterfaceOnly in the future
Please note UserInterfaceOnly behaves differently in less recent versions

See below explanation of UserInterfaceOnly

Protect Excel Worksheet as UserInterFaceOnly - PK: An Excel Expert

Learn how to expand or collapse groups and enter data in a Protect Excel Worksheet using VBA with Sprinter Face Only in Excel.

www.pk-anexcelexpert.com

 

[squirrellydw]

Ok so just copy that code where it is currently is and put my password in? I will try that next week when I return. Thanks again for all the help

 

[MARK858]

Ok so just copy that code where it is currently is and put my password in?

Yes password/s as indicated