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

 

[Artik]

The statement about earlier versions is from Microsoft in their Worksheet.Protect method (Excel), so if you feel it is incorrect then you had best take it up with them

That's what I guessed from the appearance. Perhaps they meant versions 5 and 95. Okay, I'll report, and we'll return to the topic in 5-10-15 years.

The statement that you need to set the parameter when you password protect the sheet is correct, as is that you can't set it manually

This is a statement I can agree with.

Artik

 

[squirrellydw]

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
View attachment 121802


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

OK one more question about this. Is there away I can use the same password for all the sheets but for the sheet named "1 Names" allow it to still be protected but be able to select "unlocked cells" and "Sort and Use Auto Filter"?

 

[MARK858]

      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

Replace the line in red above with the lines in red below

    Me.Hyperlinks.Add Anchor:=Me.Cells(iRow, 1), _
        Address:="", _
        SubAddress:="Start_" & wks.Index, _
        TextToDisplay:=wks.Name
    
    If wks.Name = "1 Names" Then
        wks.Protect Password:="your password for 1 names sheet", DrawingObjects:=True, Contents:=True, Scenarios:=True _
                , AllowSorting:=True, AllowFiltering:=True
    Else
        wks.Protect "your password for the other sheets"
    End If
End If

 

[squirrellydw]

      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

Replace the line in red above with the lines in red below

    Me.Hyperlinks.Add Anchor:=Me.Cells(iRow, 1), _
        Address:="", _
        SubAddress:="Start_" & wks.Index, _
        TextToDisplay:=wks.Name
   
    If wks.Name = "1 Names" Then
        wks.Protect Password:="your password for 1 names sheet", DrawingObjects:=True, Contents:=True, Scenarios:=True _
                , AllowSorting:=True, AllowFiltering:=True
    Else
        wks.Protect "your password for the other sheets"
    End If
End If

not working, do I need to remove the other parts that you added to protect the sheet?

 

[MARK858]

What other parts, you replace the one red line with the other red lines otherwise the rest of the code is the same as the previous post unless 1 Names is the sheet with the code

 

[squirrellydw]

in post 24, you had add about 4 lines that protected the sheet. Do I replace all of them with this new one or just the last one?

 

[MARK858]

There were 2 lines unprotecting the sheets and 2 lines protecting the sheets, you replace the one line protecting the sheets in the position as indicated in my post

 

[squirrellydw]

There were 2 lines unprotecting the sheets and 2 lines protecting the sheets, you replace the one line protecting the sheets in the position as indicated in my post

ok, I did that and it's not working. Run-time error 1004 and says password supplied is incorrect. I double checked and it correct. Any idea?

 

[MARK858]

Any idea?

No afraid not but you don't normally get a password is incorrect message if the sheet is unprotected as it has no password, which it should be at that stage if the password is the same as the other sheets as you stated because it got unprotected at the line below

wks.Unprotect "your password for the other sheets"

unless it is the sheet with the code in it

 

[squirrellydw]

No afraid not but you don't normally get a password is incorrect message if the sheet is unprotected as it has no password, which it should be at that stage if the password is the same as the other sheets as you stated because it got unprotected at the line below

wks.Unprotect "your password for the other sheets"

unless it is the sheet with the code in it

thank you, I got it working. My stupid self had a o instead of a 0