PDA

View Full Version : VBA to Format Charts



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

SamT
12-22-2014, 11:38 AM
Rajendran,

Welcome to VBA Express, I am sure one of our Chart experts will be along shortly.

I changed the thread title to attract them soonest. :D

If you notice, all your code is nicely indented. You can do this by Selecting the code and clicking the # icon, or clicking the icon frirt, then pasting your code in between the two new code tags

Dave
12-22-2014, 08:51 PM
U haven't shown how you've assigned the named ranges for the x & y values. If you don't want to include blank values don't put them in your named range to begin with. As far as your code missing syntax, I'm sure others will assist. HTH. Dave

rajendran
12-23-2014, 07:55 AM
Hello Dave,


Dim Pool_Rng1 As Range, Pool_Rng2 As Range
' Set date range for pool plot
PoolDataSht.Activate
Set Pool_Rng1 = Range(Cells(StartRow, 1), Cells(EndRow, 1))
Set Pool_Rng2 = Range(Cells(StartRow, 2), Cells(EndRow, 2))

Does the above answer your question?

krishnak
12-23-2014, 09:00 AM
One way will be to record the code while formatting the charts manually. But I understand that the VBA code for charts is sketchy in Excel 2007.

rajendran
12-23-2014, 10:48 AM
"Record Macro" feature is very limited. Before submitting my question here, I tried your suggestion. The manual formatting keystrokes did not get recorded.

Dave
12-24-2014, 08:07 AM
Pool_Rng1 & PoolRng2.... your code doesn't mention these? I was referring to Sump_Date, Sump1_RngTot, etc ..... the named ranges for the X & Y chart values. How are these set? Don't include blank values in these ranges. Haven't been able to get the syntax right that you're looking for. Good luck. Dave