rajendran
12-22-2014, 08:52 AM
This is my first psot to the list. Please bear with me if I fail to follow all the instructions. I have gone through the database and it does not look like there is a solution to my question.
I am developing an Excel VBA code for plotting measured flow values over time. The total flow comes from two sources. So, I would like to see a clusterd colum type chart with the Total, Source 1 and Source 2 values (which are ibtained at discrete dates) plotted against time. The code that I have written so far produces the following graphic.
12639
12640The second graph was obtained by manually formatting the first graph, because I could not find VBA coding for changing the width, thickness and end type for the bars. These are the methods associated with borderstyle. Is there a way to code the borderstyle attributes (width, compund type, dash type, cap type, join type)? I am using Excel 2007.
Also, I would like to show only those dates along the X-axis on which the data was collected. Is there a way to accomplish this thought?
The VBA code used is shown below.
With ActiveChart
.ChartType = xlColumnClustered
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = Sump_Date
.SeriesCollection(1).Values = Sump1_RngTot
.SeriesCollection(1).Name = "Sump No. 1, Monolith 5 - Total"
.SeriesCollection.NewSeries
.SeriesCollection(2).XValues = Sump_Date
.SeriesCollection(2).Values = Sump1_RngJt
.SeriesCollection(2).Name = "Sump No. 1, Monolith 5 - Structure"
.SeriesCollection.NewSeries
.SeriesCollection(3).XValues = Sump_Date
.SeriesCollection(3).Values = Sump1_RngFdn
.SeriesCollection(3).Name = "Sump No. 1, Monolith 5 - Foundation"
.SetElement (msoElementLegendTop)
For k = 1 To 3
.SeriesCollection(k).Select
With Selection
Select Case k
Case 1
' .Name = DataName(k)
.Interior.Pattern = xlSolid
.Interior.PatternColor = RGB(0, 0, 255)
.Border.Color = RGB(0, 0, 255)
' With .borderStyle 'Does not work
' .LineStyle = xlContinuous 'Does not work
' .Width = 4 'Does not work
' .BarShape = xlmiter 'Does not work
' endwith
' End With
If k = 1 Then .Border.Color = RGB(0, 0, 255)
Case 2
If k = 2 Then .Border.Color = RGB(0, 255, 0)
Case 3
If k = 3 Then .Border.Color = RGB(255, 0, 0)
End Select
End With
Next k
Thank you.
Rajendran
I am developing an Excel VBA code for plotting measured flow values over time. The total flow comes from two sources. So, I would like to see a clusterd colum type chart with the Total, Source 1 and Source 2 values (which are ibtained at discrete dates) plotted against time. The code that I have written so far produces the following graphic.
12639
12640The second graph was obtained by manually formatting the first graph, because I could not find VBA coding for changing the width, thickness and end type for the bars. These are the methods associated with borderstyle. Is there a way to code the borderstyle attributes (width, compund type, dash type, cap type, join type)? I am using Excel 2007.
Also, I would like to show only those dates along the X-axis on which the data was collected. Is there a way to accomplish this thought?
The VBA code used is shown below.
With ActiveChart
.ChartType = xlColumnClustered
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = Sump_Date
.SeriesCollection(1).Values = Sump1_RngTot
.SeriesCollection(1).Name = "Sump No. 1, Monolith 5 - Total"
.SeriesCollection.NewSeries
.SeriesCollection(2).XValues = Sump_Date
.SeriesCollection(2).Values = Sump1_RngJt
.SeriesCollection(2).Name = "Sump No. 1, Monolith 5 - Structure"
.SeriesCollection.NewSeries
.SeriesCollection(3).XValues = Sump_Date
.SeriesCollection(3).Values = Sump1_RngFdn
.SeriesCollection(3).Name = "Sump No. 1, Monolith 5 - Foundation"
.SetElement (msoElementLegendTop)
For k = 1 To 3
.SeriesCollection(k).Select
With Selection
Select Case k
Case 1
' .Name = DataName(k)
.Interior.Pattern = xlSolid
.Interior.PatternColor = RGB(0, 0, 255)
.Border.Color = RGB(0, 0, 255)
' With .borderStyle 'Does not work
' .LineStyle = xlContinuous 'Does not work
' .Width = 4 'Does not work
' .BarShape = xlmiter 'Does not work
' endwith
' End With
If k = 1 Then .Border.Color = RGB(0, 0, 255)
Case 2
If k = 2 Then .Border.Color = RGB(0, 255, 0)
Case 3
If k = 3 Then .Border.Color = RGB(255, 0, 0)
End Select
End With
Next k
Thank you.
Rajendran