How Protect chart source links but allow change font and colors etc?

[Gunnar Johansson]

Hi,

I find that neither "Protect Worksheet for Contents" nor "Protect Worksheet
for Contents" are suitable. I want to protect charts from changing source
and delete and cut them. However, I also want to be free to change fonts and
colors etc.

How can I make this happend? Anybody already having done this and can share
the code? Can I take some action with vba on sheet_change event somehowe?

This are the options I find that doesn't give me this possibility:

- Protect Worksheet for Contents
This option prevents changes to the chart's formats. Chart elements may be
selected, but may not be changed. This option also prevents changes to a
chart's data links, either through the series formula or through the Source
Data dialog. If there are changes to worksheet data that the chart is linked
to, the chart will update. Any objects (text boxes or shapes) on the chart
are not protected.

- Protect Worksheet for Contents
This option prevents changes to any objects (text boxes or shapes) on the
chart. If the text box is linked to a worksheet cell, and the contents of
the cell change, the textbox will update. The formatting and source data of
the chart may be changed.

/
Kind regards

(Same message in microsoft.public.excel.programming)

 

[Jon Peltier]

Gunnar -

If you hold shift while selecting a chart (to get white handles, not black), then
double click on it, you can uncheck the "Locked" box on the protection tab, and the
chart can still be selected if the sheet is protected. Then in VBA, you can fine
tune the protection of the chart with these lines:

activechart.ProtectFormatting=False
activechart.ProtectData=True

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______