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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.