none
A developer to lock-down an embedded chart ??? RRS feed

  • Question

  • Greetings to all!

    I am creating dashboards in Excel 2010. I am using VBA to program several linked, embedded, column charts. When a user clicks on a particular column in Chart1, Chart2 responds by updating its data appropriately. When a column of Chart2 is clicked, Chart3 responds. I use the MouseUp event to trap a selection, fetch the item selected via the GetChartElement method, then use the SeriesCollection.Values method to reconfigure the downstream chart. Again, please note that these are embedded charts - not chart sheets.

    This seems to be working quite well - except that there doesn't appear to be a good way to lock down the chart, yet still allow it to respond to clicks. If this is indeed the case, then is this something that an Office developer could address? Could this be handled with custom code?

    My agency might be willing to pay for some custom coding that I could apply to my dashboards. Please advise.


    Thanks!

    Thursday, September 18, 2014 5:03 PM

Answers

  • Did you had a look at this link about protecting Charts?

    http://peltiertech.com/Excel/ChartsHowTo/ChartProtection.html

    I am not sure if that is what you are after, but it will give you some options.

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    • Marked as answer by Looney2nes Thursday, September 18, 2014 6:56 PM
    Thursday, September 18, 2014 6:21 PM

All replies

  • Did you had a look at this link about protecting Charts?

    http://peltiertech.com/Excel/ChartsHowTo/ChartProtection.html

    I am not sure if that is what you are after, but it will give you some options.

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    • Marked as answer by Looney2nes Thursday, September 18, 2014 6:56 PM
    Thursday, September 18, 2014 6:21 PM
  • Daniel, no I did not see this Peltier page! I did a quick search, but I think I used the term "lock" instead of "protect", which was dumb. I'm going to check out these techniques and see if they work.

    But in general, in case Peltier's suggestions don't provide what I need, do you know if programmers capable of "lifting the hood" on Excel could offer any more (or more nuanced) protections than I could get via VBA or the interface?

    THANK YOU!
    Thursday, September 18, 2014 6:57 PM
  • No but there are MVP's and other experts active at this Forum, who might be able to give you specific answers on specific issues you encounter.

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    Thursday, September 18, 2014 7:29 PM
  • I think I follow your objective but unfortunately there isn't a straightforward way to achieve it. If the chart is protected it won't respond to application level events. In theory using different methods can trap a mouse click at any given pixel over the sheet and determine if a given chart element is underneath it and proceed from there; but convoluted, complicated and with potentially risky side effects!

    But there might be other ways, various approaches come to mind but would depend on the overall scenario. One approach might be to maintain two charts, an original hidden and protected, and an unprotected copy for the user to view and click (it needn't even link to the original cells). If the makes any changes immediately restore the copy as-was. Only a thought, if you don't think viable come back with more details.




    Thursday, September 18, 2014 8:47 PM
    Moderator