PDA

View Full Version : Change Chart Source Range Using Drop Down Box



rossmiddleto
01-11-2011, 07:22 AM
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
01-11-2011, 08:46 AM
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
01-11-2011, 08:51 AM
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


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
01-11-2011, 09:28 AM
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
01-11-2011, 10:02 AM
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
01-11-2011, 11:11 AM
First bit is the cell where the data validation is stored.

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

rossmiddleto
01-12-2011, 02:41 AM
Thank you kindly, it works like a charm.

Ross

rossmiddleto
01-12-2011, 06:59 AM
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?


Const WS_RANGE As String = "H2" '<<<< change to suit

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:


Cons WS-RANGE as String = range("B2").end(xlright)

Kind Regards

Ross

Bob Phillips
01-12-2011, 08:14 AM
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

rossmiddleto
01-13-2011, 10:30 AM
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:


Range("B2").End(xlToRight))

Bob Phillips
01-13-2011, 10:48 AM
H2 is actually redundant in that code now, it will dynamically allocate dependent upon the data.