Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 13th, 2009, 04:11 AM
Registered User
 
Join Date: Feb 2009
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default set horizontal axis of excel chart to date or other value specify myself

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
 
Old February 13th, 2009, 06:23 AM
Registered User
 
Join Date: Jan 2009
Posts: 8
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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
 
Old February 15th, 2009, 09:09 PM
Registered User
 
Join Date: Feb 2009
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default set horizontal axis of excel chart to date or other value specify myself

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

Last edited by hannah85; February 15th, 2009 at 09:36 PM..
 
Old February 15th, 2009, 11:23 PM
Registered User
 
Join Date: Jan 2009
Posts: 8
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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.
 
Old February 16th, 2009, 02:57 AM
Registered User
 
Join Date: Feb 2009
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default set horizontal axis of excel chart to date or other value specify myself

hi,

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

Regards,
hannah





Similar Threads
Thread Thread Starter Forum Replies Last Post
[VB2005]adding second y-axis to chart bellaelysium VB How-To 2 June 17th, 2008 01:16 AM
Coloring the chart plot area based on x-axis scale simranjeev Reporting Services 1 October 25th, 2007 02:50 PM
Chart Axis label Colour dmunro Reporting Services 1 September 28th, 2006 03:30 PM
Problem in formating Y axis of Chart mayuri Crystal Reports 0 December 26th, 2005 08:15 AM
Adding a second value axis to chart..? rstober Excel VBA 0 October 1st, 2003 02:47 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.