Problems with Protected Cells & Links

[pgerardi]

I currently send out files to inviduals where the majority of cells are locked, the only ones that are unlocked are for data entry. Within these files there are links. I email these files out to the individuals and they in turn complete and return. The problem is with my links. When the files are returned the links are changed from what I have on my file (location on network drive) to wherever they saved and worked on (eg their c drive) thus breaking my link. Is there a way to prevent this from happening?

Thanks for your time!

 

[morleyuk]

In a word No unfortunately.

One solution is:

Don't e-mail the workbooks but keep them in a central folder where all can access then just send them the link.

Other than that you will have to use the Edit Links to change the source back once they are returned.

 

[pgerardi]

Thanks, thats what I was afraid of. Just a pain to unprotect all of the sheets, make changes and protect them again. Unfortuantely cannot do a central folder.

 

[morleyuk]

Try this in a module makes life a little quicker:

Attribute VB_Name = "Protection"
Dim Shts As Worksheet
Private Sub UnProtectAllSheets()
'Unprotects ALL worksheets
Application.ScreenUpdating = False
For Each Shts In ThisWorkbook.Worksheets
Shts.Unprotect Password:="XXX"
Next
Application.ScreenUpdating = True
End Sub

Sub ProtectAllSheets()
'Protects ALL worksheets
Application.ScreenUpdating = False
For Each Shts In ThisWorkbook.Worksheets
Shts.Protect Password:="XXX"
Next
Application.ScreenUpdating = True
End Sub

 

[pgerardi]

Thanks, unfortuantely I get an error theats says

Compile error:
syntax error