Toggle Data Label "Value" On and Off

  • Thread starter Thread starter jgrappy
  • Start date Start date
J

jgrappy

Is there any way to toggle the "Value" label for a chart on and off
using a button linked to some VBA code. I'd like to be able to have
the label formatted (i.e. color, size, etc) so that the user just has
to click a button for the values to show on the bars of the chart.
Thanks for any help you can give!

-Josh
 
Found my own answer through recording macros. Here is the code I used,
applying it to a toggle button.

Private Sub ToggleButton1_Click()
Application.ScreenUpdating = False
If ToggleButton1.VALUE = True Then
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ApplyDataLabels AutoText:=True,
LegendKey:= _
False, ShowSeriesName:=False, ShowCategoryName:=False,
ShowValue:=True, _
ShowPercentage:=False, ShowBubbleSize:=False
ActiveChart.SeriesCollection(2).DataLabels.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
Windows("GanttChartTemplatewc.xls").SmallScroll Down:=3
ActiveWindow.Visible = False
Windows("GanttChartTemplatewc.xls").Activate
Range("Q36").Select
ActiveWindow.SmallScroll Down:=-6
Else
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ApplyDataLabels AutoText:=True,
LegendKey:= _
False, ShowSeriesName:=False, ShowCategoryName:=False,
ShowValue:=False, _
ShowPercentage:=False, ShowBubbleSize:=False
Windows("GanttChartTemplatewc.xls").SmallScroll Down:=3
ActiveWindow.Visible = False
Windows("GanttChartTemplatewc.xls").Activate
Range("N36").Select
ActiveWindow.SmallScroll Down:=-6
Application.ScreenUpdating = True
End If
End Sub
 
Back
Top