VBA macros not working with protected sheet

[Corey_Riley]

Hi Everyone,

I'm new to VBA but I was able to create the following code that allows me to click a hyperlinked button which takes me to a hidden worksheet in my workbook and hides the original worksheet.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

linkto = Target.SubAddress
wherebang = InStr(1, linkto, "!")
If wherebang > 0 Then
mysheet = Replace(Left(linkto, wherebang - 1), "'", "")

Worksheets(mysheet).Visible = True

Worksheets(mysheet).Select
myaddr = Mid(linkto, wherebang + 1)
Worksheets("Introduction").Visible = False

Worksheets(mysheet).Range(myaddr).Select

End If

End Sub

I want to share my document with my team but I need to protect my document so they can't mess up the coding or structure of the document.

When ever I protect my document an click on one of the buttons, the following message comes up:

I'm not sure what to do. Please help. (There is no password set for the protection function)

 

[Jimmypop]

Hi there and welcome to the forum... which sheet the one that needs to be protected?

Could maybe try...

Option Explicit
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    linkto = Target.SubAddress
    wherebang = InStr(1, linkto, "!")
    If wherebang > 0 Then
        mysheet = Replace(Left(linkto, wherebang - 1), "'", "")
        Worksheets(mysheet).Unprotect
        Worksheets(mysheet).Visible = True
        Worksheets(mysheet).Select
        myaddr = Mid(linkto, wherebang + 1)
        Worksheets("Introduction").Visible = False
        Worksheets(mysheet).Range(myaddr).Select
    End If
    Worksheets(mysheet).Protect
End Sub

 

[Corey_Riley]

This is the message it comes up with:

 

[Jimmypop]

You will need to declare linkto as a Variable... Or just try to remove option explicit from the top of code...

Only back at my PC tomorrow unfortunately...

 

[Corey_Riley]

That's okay. I'll wait until tomorrow.

Thank you

 

[Corey_Riley]

I've found a way around it.

I just protect each sheet individually and select all of the tick boxes.

I'm still not sure why the macro wouldn't work though