Add horizontal axis labels - VBA Excel

1
votes

I have a macro that creates a graph. I want VBA to read a range from the spreadsheet and use the values for horizontal axis labels. Basically I want to make this graph:

enter image description here

look like this (add the months on the bottom)

enter image description here

Thanks!

Macro:

Sub AddChartSheet()

   'Variable declaration
   Dim chtChart As Chart
   Dim name1 As String

   'Name is currently used for the title of the new tab where the chart is     created and the chart title
   name1 = "AHU-10-8"

   'Create a new chart.
   Set chtChart = Charts.Add
   With chtChart
  '.Name is the name of the tab where the new Chart is created
  .Name = name1

  .ChartType = xlLine
  'Link to the source data range.
  .SetSourceData Source:=Sheets(3).Range("A1:B5861"), _
     PlotBy:=xlColumns
  .HasTitle = True
  .ChartTitle.Text = name1
  .Axes(xlCategory, xlPrimary).HasTitle = True
  .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"
  .Axes(xlValue, xlPrimary).HasTitle = True
  .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Valve Position (-)"

  myFileName = name1 & ".png"
  chtChart.Export Filename:=ThisWorkbook.Path & "\" & myFileName, Filtername:="PNG"

   End With
End Sub
3
vbaexcelchartsaxis-labels

3 Answers

3
votes

To adjust the data series you are using for the Date (horizontal axes). You can either add the following 

ActiveSheet.ChartObjects("Chart 15").Activate
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$D$5:$D$19"

Note: you will first need to select the chart and adjust the range that I have to the range that you need it to be.

OR you can add

.SeriesCollection(1).XValues = "=Sheet1!$D$5:$D$19"

Between your code

.SetSourceData Source:=Sheets(3).Range("A1:B5861"), _
 PlotBy:=xlColumns

and

.HasTitle = True
0
votes

Can you share your macro or workbook?

I am not sure how you have your data setup, but you can change the format of the data that you have selected for your horizontal labels to be a date format. Or, in VBA you can change the selection to a number format of "mmmm" to just show the months.

Selection.NumberFormat = "mmmm"
0
votes

The following should work. Remember to adjust the chart name in the following code

ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "mmmm"