PDA

View Full Version : Semi-Generic Bar charts



stanl
05-01-2014, 02:05 PM
I am creating an Excel tab from an Access crosstab query that indicates call dispositions by hour, i,e,


Hour Dispo1 Dispo2 Dispo3

8-9am 10 4 29

9-10am 22 22 6

etc....


The dispositions are generic and perhaps one might be missing or added on a given day.

I have a separate tab with a simple bar chart showing say Dispo3 numbers per hour. What I would like to do given the Hours are constant, is create some sort of dropdown so the user could select the Dispo and the chart would be re-drawn. I'm hoping someone has done something similar and can give me pointers.

TIA

Stan

Bob Phillips
05-01-2014, 04:21 PM
I'd use VBA Stan.

Assumptions:
- data is on a sheet called dataSheet, and starts in A1
- chart is on a sheet called chartSheet, not a sheet of type chart, a worksheet
- the cell H1 on chartSheet has a data validation in H1 with values of 1,..., n where n is you max Dispo

Then this event code in chartSheet code module should do it



Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim rng As Range
Dim lastrow As Long

Set ws = Worksheets("dataSheet")
If Not Intersect(Target, Me.Range("H1")) Is Nothing Then

lastrow = ws.Cells(Me.Rows.Count, "A").End(xlUp).Row
Set rng = Union(ws.Range("A1").Resize(lastrow), ws.Cells(1, Target.Value + 1).Resize(lastrow))
Me.ChartObjects(1).Chart.SetSourceData Source:=rng
End If
End Sub

stanl
05-02-2014, 09:00 AM
I like that. Thanks

Stan