Macro to Update Spreadsheet Links

[Glenn Robertson]

Hi,

I have created some worksheets for people in my team to
use. The worksheets are all linked and the links update
when the workbook is opened. As I have protected the
worksheet no one can change the formulas but they also
can't update the links manually. I would like a macro
that they can click that will update the links.

Is this possible.....

Thanks
Glenn

 

[Harlan Grove]

I have created some worksheets for people in my team to
use. The worksheets are all linked and the links update
when the workbook is opened. As I have protected the
worksheet no one can change the formulas but they also
can't update the links manually. I would like a macro
that they can click that will update the links.

Your macro would need to unprotect worksheets, update links, then reprotect
worksheets in order to do what you want while maintaining protection. The
following is a minimal version of what you need.

Sub UL()
Application.EnableCancelKey = xlDisabled
ActiveSheet.Unprotect Password:="foobar"
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
ActiveSheet.Protect Password:="foobar"
Application.EnableCancelKey = xlInterrupt
End Sub