Add horizontal axis labels - VBA Excel

Question

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:

look like this (add the months on the bottom)

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

rdt0086 · Accepted Answer · 2016-03-04 22:20:51Z

4

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

answered Mar 4, 2016 at 22:20

rdt0086

1386 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rdt0086 · Accepted Answer · 2016-03-04 21:35:06Z

0

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"

edited Mar 4, 2016 at 21:35

answered Mar 4, 2016 at 21:17

rdt0086

1386 bronze badges

2 Comments

Jimbo Over a year ago

I added the macro in the OP. Thanks!

Jimbo Over a year ago

My issue is that I don't know how to select the data for the horizontal label. The numbers that appear are default.

rdt0086 · Accepted Answer · 2016-03-04 21:47:17Z

0

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"

answered Mar 4, 2016 at 21:47

rdt0086

1386 bronze badges

3 Comments

Jimbo Over a year ago

Ok, now I'm getting months on the x-axis but I get a different month per reading. I have a different column with the date in the following format: 2/1/2016. Is there a way to use that to get the actual month? Thanks!

rdt0086 Over a year ago

What type of data are you using now? You can adjust to use the other date column by changing your range.

Jimbo Over a year ago

But that is my initial issue. How do I assign a range to the x-axis labels through vba?

Collectives™ on Stack Overflow

Add horizontal axis labels - VBA Excel

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related