Protect an embedded chart..

Hi All,

I'm trying to protect an embedded chart so that it can be selected (activated) by the user clicking on it but otherwise all changes will be made by code.

I've tried using

Please Login or Register  to view this content.

but keep getting a runtime 1004.

The worksheet it will be embedded in will be protected but the chart needs to be selectable to respond to mouse events.

Any ideas?

Thanks Andy, not sure my first post was as clear as it should have been.

What I'd LIKE to do is to stop users from resizing, moving, changing data etc on the chart.. but at the same time leave it selectable on click.

There is the 'Locked' property for the 'shape' object that holds the chart but if this is set to false then the user can still mess with the chart when the worksheet is protected as well as just selecting it.

What I suppose I'm looking for is something like the 'EnableSelection' property that applies to cells (but sadly appears to be only cells!).

Any ideas?

Is this example for a chart sheet or an embedded chart?

I've considered using a 'non-embedded' chart but since the chart click produces a report in a cell range just under the embedded chart then it would kind of defeat the object if the user had to click between sheets.

I've just found this

Worksheets(1).Shapes(1).OLEFormat.Activate

in the Excel help file.. no idea what it does yet but will keep you posted.

No.. just failed to read your post properly! Sorry!

I've added your code to the class module for my chart.. it has potential.. again, I'll keep you posted.

Andy, I've tried your code on an embedded chart but without success. I'm getting the feeling this is a dead end.

I think the best option in this case might be to just save the default settings for the chart and re-apply them after the user does something they shouldn't such as resizing the chart. It's not ideal as there's loads of changes to trap but I can't see another option.

Thanks for your suggestions Andy, much appreciated.

Thanks for this Andy,

I've opened it up to have a look and can see what you've done.

When I click on the chart Excel hangs (task maanger shows 100% cpu too!)..

I'm not sure but I think it may be looping itself to infinity!

I'm trying to spot whether one event is causing another to fire but it seems you've cancelled most of them... GRRR!!!

This is now bugging me!

BTW I'm running Excel 2007.. I'm not sure if there might be a version difference.

Will give it a go.. cheers.

Nice one..

I've supressed a couple of the spurious events using 'Application.EnableEvents = False' and it seems to do the trick for the resize problem. Good code!

I'd pretty much finished writing code to create the whole chart from scratch on the fly, which kind of solves the problems. There's still the possibility of user messing up what's inside the parent object (although this really is their problem!) so I'm going to use some of your code, however I'll recreate the entire chart periodically when there are major data changes.

Problem solved.. well, hacked at least!

Thanks for all your help Andy, much appreciated.

I still wish I knew whether the protect method appied to embedded charts as well though.. hey ho.. life is probably too short to work out microsoft's help files!

hi I had the same issue you did and I found this very promising bit of code (here :http://peltiertech.com/Excel/ChartsH...rotection.html)

ActiveSheet.ChartObjects(1).Chart.ProtectSelection = True

It leaves the possibility to resize the chart itself but still i'm pretty satisfied

I'll update when my project's over