Change Chart Source Range Using Drop Down Box

[rossmiddleto]

Hi Everyone,

How do I insert a line graph into my spreadsheet where the source cells change dependent on a drop down selection box?

Each selection in the drop down box will refer to a new row of cells. For example, the chart for selection A in the slection box will refer to the range of cells: B3 to Range("b3").End(xlToRight).Offset(0, -2) as the source cells for the chart....

The chart for drop down box selection B in the dro down box will refer to the range of cells C3 to Range("C3").End(xlToRight).Offset(0, -2) as the source cells for the chart.

Kind Regards

Ross

[rossmiddleto]

Appologies, i meant to say that there will be x amounts of cells of data inbetween b3 and ("b3").End(xlToRight).Offset(0, -2). This amount of cells will change evey time I run another macro so I need the chart drop down menue to realise this.

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H2" '<<<< change to suit
Dim rng As Range

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

Select Case .Value

Case "A":

Set rng = Me.Range(Me.Range("B3"), Me.Range("B3").End(xlToRight).Offset(0, -2))
Me.ChartObjects("Chart 1").Chart.SetSourceData Source:=rng

Case "B"

Set rng = Me.Range(Me.Range("C3"), Me.Range("C3").End(xlToRight).Offset(0, -2))
Me.ChartObjects("Chart 1").Chart.SetSourceData Source:=rng
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[rossmiddleto]

Hi xld, thanks for your answer. When you say "Change to suit" in your program what do I change the value "h2" to?

Does it need to be the cell reference, or either the value A or B?

Kind Regards

Ross

[rossmiddleto]

Hi Xld,

I have figured out that H1 is the cell reference where I put in either A or B to represent case A or B, (how stupid of me!) but I am getting the following error for this line of code:

Me.ChartObjects("Chart 1").Chart.SetSourceData Source:=rng


Run-time error. '-2147024809 (80070057)':
The Item with the specified name wasn't found


Any ideas?

[Bob Phillips]

First bit is the cell where the data validation is stored.

The error is probably because the chart is not called Chart 1.

[rossmiddleto]

Thank you kindly, it works like a charm.

Ross

[rossmiddleto]

Just anohter quick one...


If i wanted the cell "H2" in your below code to intead of refering to a static cell, refering to a changing cell reference how would I do this?

[VBA]
Const WS_RANGE As String = "H2" '<<<< change to suit
[/VBA]
I would like it to refer to the cell that is always at the right end of row b. I have tried using the following but with no luck:

[VBA]
Cons WS-RANGE as String = range("B2").end(xlright)
[/VBA]
Kind Regards

Ross

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H2" '<<<< change to suit
Dim rng As Range

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range("B2").End(xlToRight)) Is Nothing Then

With Target

Select Case .Value

Case "A":

Set rng = Me.Range(Me.Range("B3"), Me.Range("B3").End(xlToRight).Offset(0, -2))
Me.ChartObjects("Chart 1").Chart.SetSourceData Source:=rng

Case "B"

Set rng = Me.Range(Me.Range("C3"), Me.Range("C3").End(xlToRight).Offset(0, -2))
Me.ChartObjects("Chart 1").Chart.SetSourceData Source:=rng
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

[rossmiddleto]

Thanks, again it works like a charm.

lastly (and I promise I will not ask anything else around this thread after my next question); how do I change the "H2" reference in your code to refet to the same cell as the Intersect(Target, Me.Range("B2").End(xlToRight)) part of your code.

My reason for wanting to do this is because I have a macro that inserts columns into a sheet. My charts are to the right of the inserted columns as is the drop down box that I use to select the chart source cells. Every time i insert a column, the cell reference of this drop down box changes (so H2 would become H3) and I need to be able to pick this up by using something like the following instead of the H2 reference:

[VBA]
Range("B2").End(xlToRight))
[/VBA]

[Bob Phillips]

H2 is actually redundant in that code now, it will dynamically allocate dependent upon the data.