Visual Basic code stopped working

squirrellydw

Board Regular
Joined
Apr 2, 2015
Messages
85
Office Version
  1. 365
Platform
  1. Windows
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"

VBA Code:
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
 
Upvote 0
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
 
Upvote 0
Rich (BB code):
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
1738197730095.png



See below explanation of UserInterfaceOnly


 
Last edited:
Upvote 0
Solution
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
 
Upvote 0
You're welcome, btw can you think about adding your Excel version to your profile please
 
Upvote 0
Perhaps language differences (English/Polish) are the reason, but I get the impression that there are several incorrect statements here. Therefore, there will be some clarification.
The addition of the UserInterfaceOnly parameter set to True in the Protect method, since this parameter was introduced (version 97), has always resulted in protection of the sheet with the ability to modify cells with code. However, the use of this parameter has always been “ volatile”, i.e. protection in this special mode worked until the workbook was closed. Once the workbook is closed and reopened, the sheet is protected in “normal” mode, as if it had been protected manually. Therefore, when we expect code to be able to change the contents of cells in this worksheet, we must call the Protect method again with UserInterfaceOnly:=True. It is not true that when the sheet has been protected manually, the Protect method with UserInterfaceOnly cannot be used. It has always been possible.
As long as the password is correct the following line of code will protect the sheet when it is unprotected or when it has been protected manually:
VBA Code:
Worksheet(“Sheet1”).Protect Password:=“MyPwd”, UserInterfaceOnly:=True
In general we insert this code in the Workbook_Open event, although with a large number of sheets to protect this is not the best place or way. So, in the way of protecting a sheet using UserInterfaceOnly nothing has changed from 97 to today. However, the password encryption algorithm has changed since the 2013 version. The new algorithm gives a greater sense (and only a sense!) of security, but it is much slower than the old version. Therefore, with a large number of sheets to protect, it takes significantly longer to execute the code.
I also want to point out that not everything can be done by code when the sheet is secured in this special mode. There are several exceptions, which you will learn about when you get an runtime error. :)

Artik
 
Upvote 0
Perhaps language differences (English/Polish) are the reason, but I get the impression that there are several incorrect statements here. Therefore, there will be some clarification.
The statement about previous 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 :rofl:

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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,226,287
Messages
6,190,059
Members
453,592
Latest member
bcexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top