locked
Setting Alternative Text for a Chart on its Own Sheet (Excel 2010) RRS feed

  • Question

  • Hello,

    I'm trying to set the alt text property of a chart. The chart is on its own sheet, called "MyChart". When I use the macro recorder, I get the line

    ActiveSheet.Shapes("Chart 1").AlternativeText = "MyTextHere"

    yet when I try to run the macro, I get the error message
        Run-time error ‘-2147024809 (80070057)’:
        The item with the specified name wasn’t found.

    After some tinkering, I have found that the code provided by the macro recorder will work for charts that are placed in another worksheet, but not for charts on their own sheet. I have tried replacing "Chart 1" with "Chart", "Chart1", "MyChart", and ActiveChart.Name, but to no avail. Any help with this would be very greatly appreciated, as it's driving me nuts!

    Many thanks,
    David


    • Edited by David Parsons Tuesday, August 23, 2011 6:49 PM Added Excel Version
    Tuesday, August 23, 2011 6:45 PM

Answers

  • Hi Tushar,

    Thanks for your response.  It is possible, however, to set the alternative text property of the chart in Excel 2010 (though in Excel 2007 it is not).  If you right-click the Chart Area and select "Format Chart Area..." > "Alt Text", you can type alternative text in the description field.  I just can't seem to do it with VBA!

    Thanks again,
    David


    Yes, you are correct that one can specify a 'alternative text' description through the Format Chart Area dialog box.  However, it is *not* a chart attribute but an attribute of the shape object containing the chart, which is why the generated code refers to shapes("chart 1").  A chart in a sheet by itself doesn't have a container parent and so there's nowhere to store the alt text.

    Try this test. With a chart in a worksheet create a alt text.  Now, move the chart to a sheet by itself and move it back to the worksheet.  No more alt text!

    IMO, Microsoft has a bug in allowing one to specify a alt text for a chart in a sheet by itself when there is no way to preserve that information!  And, it should warn the user if a chart, being moved to a sheet by itself, has shape attributes, like alt text, that will be lost.

    Here's another test you can do.

    Create a chart in a worksheet, add the alt text and save the sheet as a HTML document.  Examine the source of that document and you will find the alt text in the img html tag.

    Now, create a chart in a sheet by itself, add the alt text and save the sheet as a HTML document. Examine the source of that document and you will find a default value of ChartN, where N is some integer, as the alt text in the img html tag.

    Bottom line.  Excel 2010 allows one to specify the alt text as though it were a chart attribute but it is not.  It's an attribute of the shape containing the chart and that shape does not exist when the chart is in a sheet by itself.


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    • Proposed as answer by danishani Sunday, March 4, 2012 11:11 PM
    • Marked as answer by danishani Thursday, March 8, 2012 6:26 AM
    Tuesday, August 23, 2011 11:50 PM

All replies

  • Hello,

    I'm trying to set the alt text property of a chart. The chart is on its own sheet, called "MyChart". When I use the macro recorder, I get the line

    ActiveSheet.Shapes("Chart 1").AlternativeText = "MyTextHere"

    yet when I try to run the macro, I get the error message
        Run-time error ‘-2147024809 (80070057)’:
        The item with the specified name wasn’t found.

    After some tinkering, I have found that the code provided by the macro recorder will work for charts that are placed in another worksheet, but not for charts on their own sheet. I have tried replacing "Chart 1" with "Chart", "Chart1", "MyChart", and ActiveChart.Name, but to no avail. Any help with this would be very greatly appreciated, as it's driving me nuts!

    Many thanks,
    David



    It's interesting that you got the macro recorder to generate that code for a chart on its own sheet because such a chart is *not* contained inside an object that supports the AlternativeText property (and the chart by itself doesn't support such a property either).

    Basically, you cannot do what you want because a chart by itself doesn't support the AlternativeText (or equivalent) property.


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Tuesday, August 23, 2011 7:45 PM
  • Hi Tushar,

    Thanks for your response.  It is possible, however, to set the alternative text property of the chart in Excel 2010 (though in Excel 2007 it is not).  If you right-click the Chart Area and select "Format Chart Area..." > "Alt Text", you can type alternative text in the description field.  I just can't seem to do it with VBA!

    Thanks again,
    David

    Tuesday, August 23, 2011 7:55 PM
  • Hi Tushar,

    Thanks for your response.  It is possible, however, to set the alternative text property of the chart in Excel 2010 (though in Excel 2007 it is not).  If you right-click the Chart Area and select "Format Chart Area..." > "Alt Text", you can type alternative text in the description field.  I just can't seem to do it with VBA!

    Thanks again,
    David


    Yes, you are correct that one can specify a 'alternative text' description through the Format Chart Area dialog box.  However, it is *not* a chart attribute but an attribute of the shape object containing the chart, which is why the generated code refers to shapes("chart 1").  A chart in a sheet by itself doesn't have a container parent and so there's nowhere to store the alt text.

    Try this test. With a chart in a worksheet create a alt text.  Now, move the chart to a sheet by itself and move it back to the worksheet.  No more alt text!

    IMO, Microsoft has a bug in allowing one to specify a alt text for a chart in a sheet by itself when there is no way to preserve that information!  And, it should warn the user if a chart, being moved to a sheet by itself, has shape attributes, like alt text, that will be lost.

    Here's another test you can do.

    Create a chart in a worksheet, add the alt text and save the sheet as a HTML document.  Examine the source of that document and you will find the alt text in the img html tag.

    Now, create a chart in a sheet by itself, add the alt text and save the sheet as a HTML document. Examine the source of that document and you will find a default value of ChartN, where N is some integer, as the alt text in the img html tag.

    Bottom line.  Excel 2010 allows one to specify the alt text as though it were a chart attribute but it is not.  It's an attribute of the shape containing the chart and that shape does not exist when the chart is in a sheet by itself.


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    • Proposed as answer by danishani Sunday, March 4, 2012 11:11 PM
    • Marked as answer by danishani Thursday, March 8, 2012 6:26 AM
    Tuesday, August 23, 2011 11:50 PM
  • Ah, I see.  Thank you for such a detailed explanation, Tushar.  I will have to find another way to store information about the chart.

    Thanks again,
    David

    Wednesday, August 24, 2011 6:32 PM
  • Ah, I see.  Thank you for such a detailed explanation, Tushar.  I will have to find another way to store information about the chart.

    Thanks again,
    David


    You are welcome.
    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Thursday, August 25, 2011 2:02 AM