Changing name and range of horizonal and vertical axis in a stacked bar chart

[evalent]

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: Capture-1.jpg

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]

What does the data look like?

[evalent]

Hello xld,
the data looks like this
Capture2.jpg

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]

Looks good to me, what do you mean by '... not consistent'?

[evalent]

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:

Capture3.jpg

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

Thanks!

[evalent]

If anyone knows any other way that is less complicated than this, I am open to suggestions!

[Bob Phillips]

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]

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]

I don't get anything like that. Can you post the workbook, I am intrigued?

[Bob Phillips]

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]

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]

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]

Good function

Thank you