Hyperlinks in unprotected cells in Protected Worksheets using VBA

Detectiveclem

Board Regular
Joined
May 31, 2014
Messages
211
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,849
Office Version
365
Platform
Windows
If you record a macro whilst you protect the sheet, it will give you the code you need.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,849
Office Version
365
Platform
Windows
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

Board Regular
Joined
May 31, 2014
Messages
211
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,849
Office Version
365
Platform
Windows
You should have ended up with something like this (depending on what you selected)
Code:
    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

Board Regular
Joined
May 31, 2014
Messages
211
Thank you Fluff, I will try this tomorrow and let you know how I get on.

Really appreciate all your assistance.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,849
Office Version
365
Platform
Windows
You're welcome
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,849
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,099,375
Messages
5,468,238
Members
406,575
Latest member
Joe00

This Week's Hot Topics

Top