+ Reply to Thread
Results 1 to 7 of 7

Legend Only Shows Used Axis on Dynamic Chart (X, Y and Y2 axis)

  1. #1
    Registered User
    Join Date
    05-21-2012
    Location
    Houston
    MS-Off Ver
    Excel 2007
    Posts
    14

    Legend Only Shows Used Axis on Dynamic Chart (X, Y and Y2 axis)

    I have a dynamic chart that allows manipulation of the X, Y and Secondary-Y (Y2) axis (controlled from a drop-down list). However, there will be times that only the X and Y axis will be used, as no data will be selected for Y2. How can I force the legend to not show a line for Y2, dynamically? Please reference the attached file.
    Thanks in advanced for your help!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Lightbulb Re: Legend Only Shows Used Axis on Dynamic Chart (X, Y and Y2 axis)

    Hi
    The only way I know to do as you require is to use a some VBA code. Now I'm no expert with VBA, so I'm not sure how good the code is in my attachment but it seems to work. If any of our forum Gurus can improve the code, or know of a none VBA solution I'd be happy to learn. There are 2 sub routines in Module1 and a worksheet Calculate event which triggers the code based on the value in cell L30, which contains a formula. One routine hides column E which removes the Secondary series and it also removes the Axis values and Title. The other routine reverses the process.

    Hope this is of some use.
    DBY
    Last edited by DBY; 06-19-2012 at 11:08 AM. Reason: Attachment removed, faulty

  3. #3
    Registered User
    Join Date
    05-21-2012
    Location
    Houston
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Legend Only Shows Used Axis on Dynamic Chart (X, Y and Y2 axis)

    I really appreciate your help with this! My skills are beyond pushed with this little project. I work with a group of people who need to output information (in a professional looking fashion) for their customers in a very short amount of time. In addition, a majority of these people have very little excel skills and exceptionally uncomfortable with the program. Therefore, I am trying to program into the tool as much of the thinking as possible. We are understaffed in our IT group, and because I understand their needs very well, have elected to take on the task - yikes! So, I am exceptionally grateful for your time and skills!

    After looking over your file, I have a very rough understanding of what is going on, but have completely made a mess of things as I transfered it to my working file. If you are interested, I will send my working file over for you to peak at, but not sure if want to venture that much more into it. It very well may be something completely simple, but who knows... Thanks again, TB

  4. #4
    Registered User
    Join Date
    05-21-2012
    Location
    Houston
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Legend Only Shows Used Axis on Dynamic Chart (X, Y and Y2 axis)

    Sorry, again i forgot to attache the file...Example 5.xlsm

  5. #5
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Legend Only Shows Used Axis on Dynamic Chart (X, Y and Y2 axis)

    Hello Tisha
    I'm not sure what's gone wrong but neither your file or the one I uploaded seem to work correctly. I tested it numerous times before uploading and it seemed fine, very strange. Anyhow, as I said I'm no expert with VBA so perhaps my code wasn't very good. I was wondering how you might want to display your chart. If you're happy to have the chart on its own sheet, then perhaps my new attachment might be a better way to go. It uses two charts, one with a secondary axis and one without. Depending on what you select it goes to that chart. It uses some VBA but hopefully this works better.

    DBY
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-21-2012
    Location
    Houston
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Legend Only Shows Used Axis on Dynamic Chart (X, Y and Y2 axis)

    I am very tempted to go that route, but I the folks I am creating it for really need as few things to click on as possible. So, I can't give up just yet. I am going to give your another look and see if a couple nights sleep has given me different perspective. I'll let you know if I figure anything out. Thanks so much for your time! TD

    Do you have your original attachment that you can send in a private message? I don't know if it is possible, but maybe I saved back the faulty version.

  7. #7
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Legend Only Shows Used Axis on Dynamic Chart (X, Y and Y2 axis)

    Hello
    I don't think you can put attachments in private messages, so I've attached my original file here (Example4_Reply), it still seems to work. I've also attached an amended version of my last reply (Example7). I've added a combo box to each chart, working from a lookup table, where you can select any other chart. It requires only 1 click and you get a full page view of each chart. I don't think it can be simpler. As I don't know the folks you're designing the spreadsheet for, I don't know what else to suggest at the moment.

    Hope this helps.
    DBY
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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