Macro to unprotect sheet, update links and reprotect sheet RRS feed

  • Question

  • We have spreadsheets that are protected to ensure users can't inadvertently change formulas. Part of the formulas include links to external spreadsheets. These external spreadsheets change every month.

    We would like the users to update the link via the edit/links function. In order to access the edit/links function the sheet needs to be unprotected. So we would like a macro that

    1. unprotects the sheet (I have this)

    2. brings up the edit/link function box and allows the user the click on the change source button, select a new source and then close the edit/link function box . Then move onto the next step of the macro

    3. reprotect the sheet (I have this).

    If I use the macro record changing a link using the edit/link function it puts this code in:   

    ChDir "S:\2011"
        ActiveWorkbook.ChangeLink Name:= _
            "S:\2012\originalfile.xls" _
            , NewName:= _
            "S:\2011\newfile.xls" _
            , Type:=xlExcelLinks

    This codes the new file name in the macro whereas I wanted to simply bring up the edit/link function box. Is there a way to do this?

    We use Excel 2003.

    • Moved by Reed KimbleMVP Tuesday, May 1, 2012 3:57 PM VBA in VB General (From:Visual Basic General)
    Tuesday, May 1, 2012 6:12 AM

All replies

  • I would recommend posting to the Excel for Developers forum. This forum is primarily for Visual Basic .NET.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, May 1, 2012 3:14 PM
  • What you might use is a Variable to change the Path/ File name on Monthly basis, so it will change automatically when month is changing too.

    Would that work?

    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    Tuesday, May 1, 2012 8:38 PM