none
Store hidden data tags in chart RRS feed

  • Question

  • Hi there

    I have an Excel VBA add in that that formats charts. Currently, when the operation is run on a chart that is within a worksheet, I have it set to come up with a message box asking the user whether the user would like to move the chart to a ChartSheet (Yes: move the chart, No: leave it where it is).

    If the user selects no, and then runs the tool again, I would like it to 'remember' the last choice and skip the message box.

    The key outcomes I'm looking for are:

    • The choice to be 'remembered' on a chart-by-chart basis; and
    • The choice to be persistent when the workbook is saved, exited, reopened and sent to other people that have the add in.

    I had thought of hiding a string in a seldom-used property of the chart class to identify what option had been selected, but I could not find a property that suited (and in any case, this doesn't sound very robust). 

    I would be grateful for any suggestions you can provide. Apologies, this is my first post, so please let me know if I've missed any important information.

    Thank you in advance.

    JkNZ12


    • Edited by jknz12 Saturday, May 6, 2017 8:59 PM
    Saturday, May 6, 2017 8:58 PM

All replies

  • Hi jknz12 -I've stored these 'settings' type values in a defined name cell either on the sheet itself (your charts are embedded) or stored all of them on a worksheet named 'Settings' and again used defined names. I use a naming scheme that associates the worksheet & chart names to the defined name governing the setting.

    -J


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com. For any reply that either helps to answer your question or is the answer, please mark it as helpful or as the answer so others with the same question will have an answer quickly.

    Monday, May 8, 2017 1:39 PM
  • Hi MainSleuth

    Thank you for your suggestion.

    Keeping the setting in the sheet sounds like a good option for some projects.  In this case the aim is to distribute the tool to people in our organisation and for them to use it on any spreadsheet, and it would be good if they couldn't see any of the 'inner workings' of the tool. Given the tool (which is an add-in) can be used on any spreadsheet, I would not be able to predict which 'cell' on a given sheet will be free to hold the setting. I could use a remote cell (e.g. YYY10000), but this would likely cause some problems when unsuspecting users go to print the document. 

    - jknz12


    Tuesday, May 9, 2017 8:48 AM
  • Defined names don't have to refer to a cell. They can refer to a value.

    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com. For any reply that either helps to answer your question or is the answer, please mark it as helpful or as the answer so others with the same question will have an answer quickly.

    Thursday, May 11, 2017 2:50 AM