not allow update for workbook w/ Links

[Guest]

Hi,

I have a workbook (excel 2003) that contains many links to other workbooks,
but I don't want the user to update those links. I need to keep the
historical data. Is there a way to prevent a user from updating them?

When a user opens up the workbook, they get a message that states "This
workbook contains links to other data sources." and then the options to
Update or Don't update.

I can't find any settings to not allow updates.

Hope someone can help!

Thanks in advance,

Mark

 

[Guest]

Try clicking Tools > Options > Edit tab,
uncheck "Ask to update automatic links" > OK
Save the book. Then re-open to test it out.

 

[Guest]

Try clicking Tools > Options > Edit tab,
uncheck "Ask to update automatic links" > OK
Save the book. Then re-open to test it out.

 

[Guest]

Try clicking Tools > Options > Edit tab,
uncheck "Ask to update automatic links" > OK
Save the book. Then re-open to test it out.

 

[Dave Peterson]

Even if the user isn't allowed to update links when they open the workbook, they
could use Edit|Links to update them or even hit F2, Enter on one of the cells
(or anything that would cause those links to be updated).

You may want to consider changing the formulas to values in the workbook that
you share with others.

 

[Guest]

Hi Max,

While your suggestion does get rid of the message that comes up, it appears
that the links are automatically updated, which is what I want to prevent
from happening.

 

[Guest]

Hi Dave, thanks for your suggestion.

The problem that I am having is this. I have workbook A which contains many
sheets, including a "production schedule" sheet that contains contains
several "Paste Link" options (off of the Paste Special selection) from other
sheets within the same workbook. The second workbook contains only these
individual "production schedule sheets". So when I have the users copy the
sheet (by r-clicking on the sheet tab>make copy>save in workbook) it brings
over the current info on that sheet.

So, when the user opens up the production schedule workbook, it asks if you
want to update the links, and if the user answers yes, then all of the sheets
will have the same info (with the current info saved from the first workbook).

This is hard to explain, that and the fact that these users are not advanced
by any means and that the copy process needs to be kept simple for them.

Thanks again,

Mark

 

[Dave Peterson]

Maybe you could provide a macro that does exactly what you want.

Give them an addin that adds the functionality that you want. That way, you'll
have more control over what's happening. The user could still have trouble if
they do things manually, but if your macro does lots of nice stuff and makes it
much easier, I (as a user) would use that instead of doing it manually.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

 

[Guest]

Thanks Dave, I will give that a try. I will print out the suggestions you
have given me. I need to read up on macro's first however, I have only done
some fairly simplistic ones that record some keystrokes, that's about it.

Thanks again!

 

[Max]

Thanks for the feedback, Mark. Glad to see Dave's responses satisfy your
need.