Graphing source data from a combo box

V

vbnoob

Board Regular
Joined
Oct 22, 2008
Messages
67
Hi,

I have got a table of results with wards along the columns and in the rows reading left to right weekly dates e.g. 5/10/08 12/10/08 19/10/08 etc.

How can I get a graph to change the data it displays from a combo box eg. A1 is selected in the combo box and it shows a line graph of the Data for A1 over the weeks? There are 50 wards :eek::eek:

Thanks

Dave
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Are you wanting to show the graph or just update the source data that it plots? You will probably need to do it manually first. Once you have it setup, post your xls to a shared site like 4shared.com, box.net or mediafire.com. We can then help best.
 
Upvote 0
I want to show a line graph based on it. So if you select A1 then it will show A1's line and no others. If you select A2 then it will show that data etc.

I am wary of putting it onto a shared site cause the information is quite sensitive.
 
Upvote 0
Dates would not be that sensitive. It is best just to make a simple one anyway. You can obfuscate any sensitive data. The more simple the better.

Sounds like all you need to do is to use a named range and reset that range based on the selection.

Show the line graph on the userform or show it after closing the userform?
 
Upvote 0
A data validation list to update a chart on that sheet is easy enough.

Renaming the range method would work but I just reset the seriescollection in this example. So, no names are needed for this method.

Right click your sheet with the chart in your example, View Code, and paste:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range, bc As Range, idx As Integer
  Set r = Range("E23")
  If Target.Address <> r.Address Then Exit Sub
  idx = WorksheetFunction.Match(r, Range("B4:B19"))
  Set bc = Range("B3")
  Me.ChartObjects(1).Activate
  With ActiveChart.SeriesCollection(1)
    .Values = "=" & Me.CodeName & "!" & Range(bc.Offset(idx, 1), bc.Offset(idx, 9)).Address(True, True, xlR1C1)
    .Name = "=" & Me.CodeName & "!" & bc.Offset(idx, 0).Address(True, True, xlR1C1)
  End With
  r.Select
End Sub

Sub ShowMyChartName()
  Dim co As ChartObject
  Debug.Print Me.ChartObjects(1).Name 'If you know that 1 chartobject exists
  For Each co In Me.ChartObjects
    Debug.Print co.Name, "charts on worksheets"
  Next co
End Sub

The 2nd Sub is optional. You can play it the VBE should you want to view the current sheet's chartobject names in the Immediate window.
 
Upvote 0
Hi it works a treat on the file I uploaded but I cannot get it to work on my real sheet. I tried Changing the variables but I get an error on this part.

idx = WorksheetFunction.Match(r, Sheets("Score History").Select "B3:B50"))

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range, bc As Range, idx As Integer
  Set r = Range("G2")
  If Target.Address <> r.Address Then Exit Sub
  idx = WorksheetFunction.Match(r, Sheets("Score History").Select "B3:B50"))
    Set bc = Range("B3")
  Me.ChartObjects(1).Activate
  With ActiveChart.SeriesCollection(1)
    .Values = "=" & Me.CodeName & "!" & Range(bc.Offset(idx, 1), bc.Offset(idx, 9)).Address(True, True, xlR1C1)
    .Name = "=" & Me.CodeName & "!" & bc.Offset(idx, 0).Address(True, True, xlR1C1)
  End With
  r.Select
End Sub

Thanks again for your help
 
Upvote 0
Select is your problem. You need to use Range as I did. If the range is on the sheet "Score History" rather than the current sheet then:
Code: 
 idx = WorksheetFunction.Match(r, Sheets("Score History").Range("B3:B50"))
 
Upvote 0
Hi,

I know this was a while ago now but we was not sure if the project was going to get canned but now it is back up and running. I have got another error and I have been trying to solve it myself but cannot seem to see what the problem is. Its on this line and I have not changed it at all since the code that worked in the example??

.Values = "=" & Me.CodeName & "!" & Range(bc.Offset(idx, 1), bc.Offset(idx, 9)).Address(True, True, xlR1C1)


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range, bc As Range, idx As Integer
  Set r = Range("E23")
  If Target.Address <> r.Address Then Exit Sub
  idx = WorksheetFunction.Match(r, Range("B4:B19"))
  Set bc = Range("B3")
  Me.ChartObjects(1).Activate
  With ActiveChart.SeriesCollection(1)
    .Values = "=" & Me.CodeName & "!" & Range(bc.Offset(idx, 1), bc.Offset(idx, 9)).Address(True, True, xlR1C1)
    .Name = "=" & Me.CodeName & "!" & bc.Offset(idx, 0).Address(True, True, xlR1C1)
  End With
  r.Select
End Sub

Thanks again
 
Upvote 0
sorry I pasted the wrong code, it is the code below with the same line that I am having trouble with .values = ...
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, bc As Range, idx As Integer
Set r = Range("G2")
If Target.Address <> r.Address Then Exit Sub
idx = WorksheetFunction.Match(r, Sheets("Score History").Range("B3:B50"))
Set bc = Range("B3")
Me.ChartObjects(1).Activate
With ActiveChart.SeriesCollection(1)
.Values = "=" & Me.CodeName & "!" & Range(bc.Offset(idx, 1), bc.Offset(idx, 9)).Address(True, True, xlR1C1)
.Name = "=" & Me.CodeName & "!" & bc.Offset(idx, 0).Address(True, True, xlR1C1)
End With
r.Select
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Dynamic Graphing - with source data from a "Unique" formula
Replies
5
Views
166
Cubist
Cubist
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
339
awoohaw
awoohaw
D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
177
dilshod_k
D
N
  • Question
Auto Selection of Combo Box from Text Box value in Activecell Row
Replies
1
Views
541
nandhavnk
N
B
  • Question
Looking for advice: Methods to update a table from a data source stored online
Replies
0
Views
276
blothian
B

Forum statistics

Threads
1,215,834
Messages
6,127,164
Members
449,368
Latest member
JayHo

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