PDA

View Full Version : [SOLVED] Changing name and range of horizonal and vertical axis in a stacked bar chart



evalent
12-22-2015, 04:33 AM
Hello everyone.

I want to generate a gantt chart via excel and VBA. So far, What I did is:


Sub chart()


Dim TheChart As chart
Dim MySheets As Sheets


ActiveSheet.Shapes.AddChart(xlBarStacked).Select
'Series#1
With ActiveChart.SeriesCollection.NewSeries
.XValues = ActiveSheet.Range("B22:B25")
.Name = ActiveSheet.Range("B22")
.Values = ActiveSheet.Range("B23:B25")
End With

'Series#2
With ActiveChart.SeriesCollection.NewSeries
.XValues = ActiveSheet.Range("D22:D25")
.Name = ActiveSheet.Range("D22")
.Values = ActiveSheet.Range("D23:D25")
End With

With ActiveChart
.HasTitle = True
.ChartTitle.Text = "Scheduling"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Text = Range("A22")
.Axes(xlCategory).ReversePlotOrder = True

.Axes(xlValue).MajorGridlines.Delete
.SeriesCollection(1).Interior.ColorIndex = xlNone
.Legend.Delete
End With

End Sub


And my graph looks like this: 15017

What I still can't do are:
1. To have the task name (Task1, Task2, Task3) in the vertical axis and Date in the horizontal axis.
2. To change the maximum and minimum range of the horizontal axis. I realized that XValues is not for the vertical axis here, however, I still can't make it work.
Have anyone encountered this before? And how should I do these?

Thanks a lot for your help!

Elizabeth

Bob Phillips
12-22-2015, 09:10 AM
What does the data look like?

evalent
12-23-2015, 12:15 AM
Hello xld,
the data looks like this
15025

and I already changed the VBA code. However, the chart resulted from the code is not consistent. I dont know where went wrong.


Sub chart()


'Dim TheChart As chart
'Dim MySheets As Sheets
Dim begindate As Date


ActiveSheet.Shapes.AddChart(xlBarStacked).Select
begindate = Range("B2")


With ActiveChart.SeriesCollection.NewSeries
.XValues = ActiveSheet.Range("A2:A4")
.Name = ActiveSheet.Range("D1")
.Values = ActiveSheet.Range("D2:D4")
End With


With ActiveChart.SeriesCollection.NewSeries
.XValues = ActiveSheet.Range("A2:A4")
.Name = ActiveSheet.Range("F1")
.Values = ActiveSheet.Range("F2:F4")
End With


With ActiveChart
.HasTitle = True
.ChartTitle.Text = "Scheduling"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Text = Range("A1")
.Axes(xlCategory).ReversePlotOrder = True


.Axes(xlValue).MajorGridlines.Delete
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Text = "Date"
.Axes(xlValue, xlPrimary).TickLabels.NumberFormat = "d-mm-yy"
.Axes(xlValue, xlPrimary).MinimumScale = begindate


.SeriesCollection(1).Interior.ColorIndex = xlNone
.Legend.Delete
End With


End Sub



Thanks!

Bob Phillips
12-23-2015, 01:53 AM
Looks good to me, what do you mean by '... not consistent'?

evalent
12-23-2015, 02:10 AM
So, the first idea is to have it updated automatically every time people change the "Date" in B2.
When I change the B2 Cell, the graph will look like this:

15026

However, if I try several time to generate the graph, I can get a result like this:
15027

Thanks!

evalent
12-23-2015, 02:16 AM
If anyone knows any other way that is less complicated than this, I am open to suggestions! :)

Bob Phillips
12-23-2015, 05:21 AM
You are confusing me now. How does changing B2 generate a completely style of chart? And what do you mean by '... if I try several time to generate the graph'?

evalent
12-23-2015, 05:28 AM
Hello xld,

That is actually my question as well.

step 1: I change B2 cell to other date (for example 22/03/15) and run the vba. The chart generated will be the first picture in the previous post (colorful bar).
step 2: I delete that colorful chart and run again the vba (B2 cell is still 22/03/15). What I meant by ' ... if I try several time to generate the graph' is that I try to do step 2 several time.
After sometimes, the chart generated will be as the second picture in the previous post (the red color bar).

Actually I don't know what went wrong. I am sorry for the confusion. :)

Bob Phillips
12-23-2015, 08:48 AM
I don't get anything like that. Can you post the workbook, I am intrigued?

Bob Phillips
12-23-2015, 03:25 PM
I finally managed to reproduce what you are seeing. This seems to fix it


Sub chart()
Dim this As Worksheet
Dim chtObj As ChartObject
Dim begindate As Date

Set this = ActiveSheet

Set chtObj = this.ChartObjects.Add(200, 100, 375, 225)
With chtObj.chart

.ChartType = xlBarStacked
begindate = this.Range("B2").Value

With .SeriesCollection.NewSeries
.XValues = this.Range("A2:A4")
.Name = this.Range("D1").Value
.Values = this.Range("D2:D4")
End With

With .SeriesCollection.NewSeries
.XValues = this.Range("A2:A4")
.Name = this.Range("F1").Value
.Values = this.Range("F2:F4")
End With

.HasTitle = True
.ChartTitle.Text = "Scheduling"

With .Axes(xlCategory, xlPrimary)

.ReversePlotOrder = True
End With

.Axes(xlValue).MajorGridlines.Delete
With .Axes(xlValue, xlPrimary)

.TickLabels.NumberFormat = "d-mm-yy"
.MinimumScale = begindate
End With

.SeriesCollection(1).Interior.ColorIndex = xlNone
.Legend.Delete
End With
End Sub

evalent
12-24-2015, 12:46 AM
Hello xld,
Sorry didnt read the message yesterday.

it is solved! Thanks a lot!
Is it because of this code?

ActiveSheet.Shapes.AddChart(xlBarStacked).Select
Do you mind to tell me what's the difference between this code and yours?

Set chtObj = this.ChartObjects.Add(200, 100, 375, 225)

I just realized that if my cursor is located in empty cell and not close to other filled cell, the resulted graph is correct. Otherwise, it creates the wrong one.

Bob Phillips
12-24-2015, 03:59 AM
I am not really sure Elizabeth. Looking at your code, I came to the conclusion that all the selecting, and not fully qualifying ranges, might be the problem, so I rewrote it as I would have written it, adding a chart object and then referencing that object.

Your observation regarding the activecell does support my thoughts on the code selecting cells I think, and maybe why I didn't see the problem for a while.

Rafea1979
12-28-2015, 06:38 PM
Good function

Thank you