VBA to automatically add series to funnel plot

D

dmhanzo

New Member
Joined
Dec 28, 2014
Messages
1
Hello All,

I’m 100% naïve to VBA so please excuse me if I appear stupid with my questions.

I have a table where 1 part is used to draw control limits (upper and lower) as a funnel plot using scatter chart as a base graph.
The second part of the tables contains a set of certain values that should be placed against the controls on the chart. The set is dynamic and could be quite large that may return overwhelming results.

Columns I used for control limits are highlighted in red in the tracker. Columns I used for certain values markers are highlighted in blue in the tracker.

I used macro recorder to get VBA code drawing Control limits and it works fine. But I have problems with recording the second set of series. I can’t record them manually every time. So my questions are.

  1. What updates are necessary to the VBA code in order to automatically place all series that are currently in the tracker?
  2. Since it is likely to be very overwhelming, is it possible to somehow split number of series on the graph (e.g. create separate charts for every 30 records, but continue using the whole table for drawing Control limits)?
  3. Is it possible and how to color series markers on the chart based on additional attribute (City (orange highlighting) in my case)?

My code is:

Code: 
Sub FunnelPlot()
'
' FunnelPlot Macro
'

'
' Drawing Control Limits

    Sheets("Sheet 1").Select
    Range("A2").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SetSourceData Source:=Range("'Sheet 1'!$A$1:$O$502")
    Application.CutCopyMode = False
'' Deleting not used series that are placed automatically
    ActiveChart.SeriesCollection(1).Delete
    ActiveChart.SeriesCollection(1).Delete
    ActiveChart.SeriesCollection(1).Delete
    ActiveChart.SeriesCollection(11).Delete
    ActiveChart.SeriesCollection(10).Delete
    ActiveChart.SeriesCollection(9).Delete
    ActiveChart.SeriesCollection(8).Delete
    ActiveChart.SeriesCollection(7).Delete
    ActiveChart.SeriesCollection(6).Delete
    ActiveChart.SeriesCollection(5).Delete
    
    ActiveChart.SeriesCollection(4).Select
    Selection.MarkerStyle = -4142
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(197, 40, 0)
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .Weight = 1
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .DashStyle = msoLineSysDot
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .DashStyle = msoLineSysDash
    End With
    ActiveChart.SeriesCollection(3).Select
    Selection.MarkerStyle = -4142
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .Weight = 1
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .DashStyle = msoLineSysDash
    End With
    ActiveChart.SeriesCollection(1).Select
    Selection.MarkerStyle = -4142
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 112, 192)
        .Transparency = 0
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .Weight = 1
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .DashStyle = msoLineDash
    End With
    ActiveChart.SeriesCollection(2).Select
    Selection.MarkerStyle = -4142
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 112, 192)
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .Weight = 1
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .DashStyle = msoLineDash
    End With
    
    ' Drawing Client values
    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(5).XValues = "='Output Site'!$K$3"
    ActiveChart.SeriesCollection(5).Values = "='Output Site'!$N$3"
    ActiveChart.SeriesCollection(5).Name = "='Output Site'!$I$2:$I$3"
    ActiveChart.SeriesCollection(5).Select
    With Selection
        .MarkerStyle = 1
        .MarkerSize = 5
    End With
    Selection.MarkerStyle = 8
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 178, 0)
        .Solid
    End With
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 0)
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 112, 192)
    End With
End Sub

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Time period reference
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Overall cases rate
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Expected cases # (adjusted to exposure)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Standard Error[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Lower Limit 95%
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Upper Limit 95%
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Lower Limit 99.5%
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Upper Limit 99.5%
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]City
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Client No.
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Exposure months
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Observed minus expected
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 768"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]
Any help is much appreciated.[TABLE="width: 768"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
 
You must log in or register to reply here.

Similar threads

F
  • Solved
Increment a series in a bubble
Replies
6
Views
169
Flavien
F
T
  • Question Question
VBA code for fill color and transparency in a 3 series line chart with only one series as an area series
Replies
0
Views
925
travellerva
T
S
  • Question Question
Select multiple graphs and apply same formatting
Replies
1
Views
417
NdNoviceHlp
NdNoviceHlp
G
  • Question Question
Draw 'X' (Diagonal lines) within selected cells and set line color
Replies
1
Views
376
sxhall
S
H
  • Question Question
Referencing a series name that contains data linked to a cell VBA
Replies
1
Views
164
Rijnsent
Rijnsent

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top