locked
Vba code for hide the horizontzal(category) Axis labels of chart(graph) RRS feed

  • Question

  • I need to hide the horizontal(category) Axis labels of chart(graph) through button.

    For example: out of 4 axis labels,need to hide only two axis labels through button.Is it possible??If it is possible please let me know the code..

    Its Urgent.Please help on this.

    Please find the screen shot of the graph in attachment.

     

    Saturday, November 14, 2015 6:10 AM

Answers

  • The simplest approach would be to delete the unwanted labels from the source cells. If necessary you could have a complete row (or column) for display and a second for the source with empty values, perhaps with some If formula to read the original.

    A different way would be to read the XValues to an array, delete values you don't want displayed, and write the array back, eg

        Set cht = ActiveChart
        Set sr = cht.SeriesCollection(1)
        xVals = sr.XValues
    
        xVals(1) = ""
        xVals(2) = ""
        sr.XValues = xVals
    Note this writes the label values to the series formula removing any connection to the source. You would need to maintain things if label values change or the source  changes size or location.

    Saturday, November 14, 2015 11:35 AM
  • The two approaches I suggested should result in removing the category text values from the axis, as you marked with an arrow in your screenshot. Did you try what I suggested?

    I don't follow what you are asking now. If you mean remove the two "0" datalabels you can simply delete the text in the labels manually or with code write "" to the datalabel captions.

    Saturday, November 14, 2015 7:17 PM

All replies

  • unfortunately, the axis labels cannot be manipulated separately.  You can, however, show none (ie: white text) and then use text boxes to label the 2 that you do want.

    Warm Regards, Crystal http://www.AccessMVP.com/strive4peace * (: have an awesome day :)

    Saturday, November 14, 2015 8:45 AM
  • The simplest approach would be to delete the unwanted labels from the source cells. If necessary you could have a complete row (or column) for display and a second for the source with empty values, perhaps with some If formula to read the original.

    A different way would be to read the XValues to an array, delete values you don't want displayed, and write the array back, eg

        Set cht = ActiveChart
        Set sr = cht.SeriesCollection(1)
        xVals = sr.XValues
    
        xVals(1) = ""
        xVals(2) = ""
        sr.XValues = xVals
    Note this writes the label values to the series formula removing any connection to the source. You would need to maintain things if label values change or the source  changes size or location.

    Saturday, November 14, 2015 11:35 AM
  • Thank you for the reply.Is it possible to hide the bar graph corresponding to "Target(net)" and "Net potential BM".

    My question is for "Target(net)" and "Net potential BM" as no values as of now.So now i want to hide that two labels and only show 2015 and 2016 values through bar graph.And in future if that gets values and show up the same.This as to be done through a button.Is it possible??

    Saturday, November 14, 2015 12:08 PM
  • Thank you for the reply.Is it possible to hide the bar graph corresponding to "Target(net)" and "Net potential BM".

    My question is for "Target(net)" and "Net potential BM" as no values as of now.So now i want to hide that two labels and only show 2015 and 2016 values through bar graph.And in future if that gets values and show up the same.This as to be done through a button.Is it possible??

    Saturday, November 14, 2015 12:09 PM
  • The two approaches I suggested should result in removing the category text values from the axis, as you marked with an arrow in your screenshot. Did you try what I suggested?

    I don't follow what you are asking now. If you mean remove the two "0" datalabels you can simply delete the text in the labels manually or with code write "" to the datalabel captions.

    Saturday, November 14, 2015 7:17 PM
  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Control Measures in Chart with Slicer and buttons (VBA macro).
    http://www.mediafire.com/file/gt4qtv45xxj1joj/01_21_20a.xlsm/file
    http://www.mediafire.com/file/26tx69w02pgzjmf/01_21_20a.pdf/file
    Much easier to control Measures by clicking on their boxes in the Field window.

    Thursday, January 23, 2020 1:53 AM