Hyperlinks in unprotected cells in Protected Worksheets using VBA

[Detectiveclem]

Hi I am having problems inserting Hyperlinks in unlocked cells on protected worksheets.

With the help of this forum I have been successfully using the code below to auto filter

However, I have noticed after this code is run, I am unable to insert a hyperlink in the unlocked cells. I have tried selecting the option to allow Hyperlinks in the unlocked cells but as soon as the following code is run the option to insert hyperlink is greyed out.

I would appreciate any help in resolving this issue


Sub LiveDan()
'
' LiveDan Macro
'
ActiveSheet.Unprotect Password:="????????"
'
ActiveSheet.Range("$B$16:$U$2006").AutoFilter Field:=6, Criteria1:= _
"<>*Completed*", Operator:=xlAnd, Criteria2:="<>*Cancelled*"

ActiveSheet.Protect Password:="?????????"
End Sub
Sub AllcontractsGina()
'
' AllcontractsGina Macro

ActiveSheet.Unprotect Password:="?????????"
'
'
ActiveSheet.Range("$B$16:$U$2006").AutoFilter Field:=6
ActiveSheet.Protect Password:="??????????"
End Sub

 

[Fluff]

If you record a macro whilst you protect the sheet, it will give you the code you need.

 

[Detectiveclem]

If you record a macro whilst you protect the sheet, it will give you the code you need.

Thanks Fluff, do you mean the whole macro or just the password bit with hyperlink being enabled....

Thanks

 

[Fluff]

Just the password bit. When protecting the sheet there are multiple arguments you can supply that will determine what you can & can't do.

 

[Detectiveclem]

Just the password bit. When protecting the sheet there are multiple arguments you can supply that will determine what you can & can't do.

The code appears to be

AllowinsertingHyperlinks:=True

but I am not sure where into my code it should be added.

 

[Fluff]

You should have ended up with something like this (depending on what you selected)

    ActiveSheet.Protect [COLOR=#ff0000][/COLOR]DrawingObjects:=True, Contents:=True, Scenarios:= _
        False, AllowInsertingHyperlinks:=True, AllowFiltering:=True[COLOR=#ff0000]
[/COLOR]

All you need to do is copy everything after the word protect & add it to your code after the password

 

[Detectiveclem]

Thank you Fluff, I will try this tomorrow and let you know how I get on.

Really appreciate all your assistance.

 

[Fluff]

You're welcome

 

[Detectiveclem]

You're welcome

Hi Fluff,

Just used your suggested code and it works perfectly. I really appreciate your help with this. VBA still baffles me, but I like the results.

Cheers,

Paul

 

[Fluff]

You're welcome & thanks for the feedback