+ Reply to Thread
Results 1 to 16 of 16

Protect an embedded chart..

  1. #1
    Registered User
    Join Date
    04-03-2010
    Location
    London England
    MS-Off Ver
    Excel 2003
    Posts
    30

    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?

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,831

    Re: Protect an embedded chart..

    If the chart is on a worksheet then do you not need to apply the protection and InterfaceOnly option to the worksheet?
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    04-03-2010
    Location
    London England
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Protect an embedded chart..

    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?

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,831

    Re: Protect an embedded chart..

    Not sure you can.

    Protecting the Interface makes the chart unselectable.

    Protecting the sheet with the Chartobject Locked makes the chart unselectable

    Protecting the sheet with the Chartobject UnLocked makes the chart selectable and editable.

    Why the need to be able to select?

  5. #5
    Registered User
    Join Date
    04-03-2010
    Location
    London England
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Protect an embedded chart..

    The chart needs to be able to respond to mousemove events and clicks, ie when the user hovers over a poit then it brings up info based on the point, and launches a sub when a point is clicked.. this all works fine until the sheet / chart is protected.

    I''ve tried adding a button to the sheet that selects the chart via code but that throws an error 'Requested shapes are locked for selection' even with the InterfaceOnly set to true..

    I guess I'll just have to leave the chart unprotected and deal with the (inevitable) consequences.

    Sigh...
    Last edited by FunkyFox; 04-14-2010 at 04:49 AM.

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,831

    Re: Protect an embedded chart..

    I did this with a chart sheet to save the hassle of setting up a chart event handler. But the principle should be the same.

    Please Login or Register  to view this content.
    When the user selects something the code cancels it.
    Mouse over still works.

  7. #7
    Registered User
    Join Date
    04-03-2010
    Location
    London England
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Protect an embedded chart..

    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.

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,831

    Re: Protect an embedded chart..

    with a chart sheet
    Sorry you are talking about your specific project, which I know nothing about.

  9. #9
    Registered User
    Join Date
    04-03-2010
    Location
    London England
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Protect an embedded chart..

    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.

  10. #10
    Registered User
    Join Date
    04-03-2010
    Location
    London England
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Protect an embedded chart..

    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.

  11. #11
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,831

    Re: Protect an embedded chart..

    Try this example.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    04-03-2010
    Location
    London England
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Protect an embedded chart..

    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.

  13. #13
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,831

    Re: Protect an embedded chart..

    I do not getting any infinite looping.

    But I would check the Resize event as changing the dimensions may cause the event to fire again. If so add a private variable to the code to set that the Resize event is in progress and quit before resizing.

  14. #14
    Registered User
    Join Date
    04-03-2010
    Location
    London England
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Protect an embedded chart..

    Will give it a go.. cheers.

  15. #15
    Registered User
    Join Date
    04-03-2010
    Location
    London England
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Protect an embedded chart..

    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!

  16. #16
    Registered User
    Join Date
    03-06-2014
    Location
    France
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Protect an embedded chart..

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1