Protecting an embedded chart, but allowing it to be copied to other aplications

[Ducati748ss]

hi

I have a spreadsheet which is mostly protected, except for a few variables to allow formatting of the embedded charts. Through VB macros I adjust the X & Y axes to allow the user to adjust the chart to get the right look. Once the user is happy with the look of the chart, they will then copy it to word or power point as an image.

However, I don't want the user to be able to delete, move, resize, change colours etc of the charts from within excel. If I lock the chart along with the rest of its sheet, he can no longer copy. If I don't lock the chart object when I lock the sheet, then the user can pretty much change anything to do with the chart object; not what I want.

Does anyone know how to lock a chart object, but allow it to be copied, so it can be pasted to another application?

Thanks
Mark

[David A Coop]

Hi Mark,

Welcome to the forum.

I don't know how to lock the sheet in the way you require.

I guess I would create a macro to do the copying. Set it up so that when the button is clicked, the macro turns protection off, saves the chart to a separate file, then turns the protection back on.

You now have a copy of the chart to use in Word or Powerpoint, but the integrity of your chart is preserved.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[Ducati748ss]

Hi David

Thanks for the idea. Yes I can do that as a work around.

However, there are 14 different charts and the user often wants to select a different subset of charts, say 4 of the 14. So the easiest way is that they pick, chart by chart, with a "control copy" and then drop this sub set into word via the clipboard, for example.

If they have to do this chart by chart, then productivity is reduced.

I guess I could write a macro to. on a button press
1) save the complete excel file as backup copy
2) switch protection off, and set an "in copy mode" flag
3) wait for a copy to clipboard event to occur, then
4) recover back to to backup copy/switch protection on

I would have to trap when in "copy mode" for save and exits etc.

or maybe

Leave the charts unprotected and have an original copy of each chart on a hidden worksheet, and whenever a "save the file" event occurs, overwrite the possibly modified charts with ones in the original format.

All a bit painful, especially when you consider copying cells, locked or unlocked, works fine. Grrrrrrr

Regards
Mark

[Ducati748ss]

double submit, same post, delete

[David A Coop]

I understand your frustration! There are always people who will try to "make improvements" regardless of the requests to leave the formatting alone.

Have you thought of saving your "master" as a template? (XLTM file)


Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".