Hi vinwin06,
Can you plz download the file from here?
http://speedy.sh/tF3Ev/Sample-Graph-2-updated.xlsm
As I could see that you have put filter at row 18 and select a particular month, I assume that you want to see the chart for a particular month. If this is your requirement then we can do this exercise more dynamic so that you do not need to put filter to show the chart for a particular month.
Explanation:
1)Placed a combobox(form control) at chart sheet to hold all the month list in the same
2)At A19(sheet1), write index formula (INDEX(DynMonth,O1)) to fetch the month selected by the user from combobox
Note: DynMonth is the named range to make month list dynamic(examine the same from name manager)
3)At B19 write the below sumproduct formula to fetch the number for a particular month and other criteria (C1 - Vol/C1 - Diffs/C1 - Surcharge/C1 - Temp/C1 - Prem/Disc C1 - Other TP/C1 - W-1 Exp/C1 - Density Exp/C1 - FX Exp/C1-FX/C1-Other )
=SUMPRODUCT(($P$4:$Z$4=B$18)*(DynMonth=$A$19)*($P$5:$Z$16)) and drag it across.
4)Now A18:L19 would be you chart range.
5)Now, if you change the month from combobox, the chart will be updated accordigly.
6)Now, regarding VBA part, we just need to find the row number for the month (selected) from A2 to A13. So the updated code would be:
[pre]
Code:
Private Sub Chart_Mousedown(ByVal Button As Long, ByVal Shift As Long, _
ByVal x As Long, ByVal y As Long)
Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim myX As Variant, myY As Double
Dim TempStat As String
Dim Tempval As String
Dim lstRow As Long
With ActiveChart
' Pass x & y, return ElementID and Args
.GetChartElement x, y, ElementID, Arg1, Arg2
' Check if we click over a point or data label?
If ElementID = xlSeries Or ElementID = xlDataLabel Then
'Here we will use Arg2 value+1 as the column number of the element (data label) selected by the user
If Arg2 > 0 Then
'Find the row number of the month selected by the user
MonthRow = Application.WorksheetFunction.Match(Worksheets("sheet1").Range("A19").Value, Worksheets("sheet1").Range("A1:A13"), 0)
'Pass the row and column reference to pick the comment for the month
Tempval = Worksheets("Sheet1").Cells(MonthRow, (Arg2 + 1)).Value
MsgBox Tempval
End If
End If
End With
End Sub
[/pre]
7)Now select the month from combobox and click on the data label to see the comment.
Kaushik
