set horizontal axis of excel chart to date or other value specify myself

hannah85 · February 13th, 2009, 04:11 AM

Hi,

I encounter problem setting the value for my horizontal axis of the excel chart. currently, i am trying to create chart using vba. i am trying to create a chart which will show the total on the vertical axis and the date(mmm/yy format) in the horizontal axis. i tried setting the horizontal axis value by setting the category name with a range i speicfy but it seems not working. could anyone help me out with this. thanks in advance.

regards,
hannah

tomj54 · February 13th, 2009, 06:23 AM

Maybe something like:

Code:

Sub Macro1()
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B10"), PlotBy :=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "ChartTitle"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Dates"
        .Axes(xlCategory, xlPrimary).CategoryType = xlTimeScale
        .Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "mmmm-yy"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Values"
        .HasAxis(xlCategory, xlPrimary) = True
        .HasAxis(xlValue, xlPrimary) = True
        .HasDataTable = False
    End With
End Sub

hannah85 · February 15th, 2009, 09:09 PM

hi,

i managed to set the value for both the x and y axis to the value i want. but after setting the value the graph wasn't plotted at all. Before setting the value for my axis the graph works well. i am not sure what is wrong with my coding. i have attached my coding below. please let me know where did i make a mistake in. thanks in advance.

regards,
hannah

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet3").Range("A1:D8"), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet3"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "ChartTitle"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Dates"
.Axes(xlCategory, xlPrimary).CategoryType = xlTimeScale
.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "mmmm-yy"
.Axes(xlCategory, xlPrimary).MajorUnitScale = xlMonths
.Axes(xlCategory, xlPrimary).BaseUnit = xlMonths
.Axes(xlCategory, xlPrimary).MinimumScale = "1/1/2008"
.Axes(xlCategory, xlPrimary).MaximumScale = "12/1/2008"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Values"
.Axes(xlValue, xlPrimary).MinimumScale = 1
.Axes(xlValue, xlPrimary).MaximumScale = 100
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
.HasDataTable = False
End With

tomj54 · February 15th, 2009, 11:23 PM

Your code produces a chart on my winxp, excel2000, but fails on vista, excel2007.

Suggest you use:

Code:

.Axes(xlCategory, xlPrimary).MinimumScale = #1/1/2008#
.Axes(xlCategory, xlPrimary).MaximumScale = #12/1/2008#

which works on both systems.

hannah85 · February 16th, 2009, 02:57 AM

hi,

thanks alot for the help. I managed to get what i want. thanks

Regards,
hannah