mantooth29
11-21-2012, 07:50 PM
Hello
I am working on a project and want to make things happen (Advanced Filter) when a user clicks on a series in an Embedded chart.
I set up the basic selection framework with test code, and everything worked fine. I then made some revisions to make my code more flexible (been reading some OOP books) and now my selection behavior is erratic.
Selecting the chart, series, data points, etc is now all over the place. Sometimes after clicking in the chart it just jumps back to a cell in the worksheet. In spite of this, the code executes as expected when I can get the selection right.
Here is the class module code, which is basically the only thing acting on these charts.
SELECTION PROCEDURE
Public WithEvents myChartClass As Chart
Private Sub myChartClass_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
Dim wsChartSheet As Worksheet
Dim varValues As Variant
Dim sArg1 As String, sArg2 As String
Dim sCritCell As String
Set wsChartSheet = myChartClass.Application.ActiveSheet 'Used to log the worksheet which
'contains the clicked chart
Select Case ElementID
Case xlSeries 'Check that selection is a Chart series
sArg1 = ActiveChart.SeriesCollection(Arg1).Name 'Get the name of the Series
If Arg2 > 0 Then 'If selection is refined to a data point, expand my output
varValues = ActiveChart.SeriesCollection(Arg1).XValues
sArg2 = varValues(Arg2) 'Get the name of narrowed datapoint
sCritCell = Criteria_Find(wsChartSheet.Name) 'gets the address of the
'First Cell being used for Advanced Filter Criteria
Criteria_Set sCritCell, sArg1, sArg2 'Output the Selections as
'Text to Control Worksheet
ElseIf Arg2 < 0 Then 'If selection not refined to one data point, do less
sCritCell = Criteria_Find(wsChartSheet.Name)
Criteria_Set sCritCell, sArg1
Else
End If
End Select
End Sub
REFERENCED FUNCTIONS
Public Sub Criteria_Set(sCritRange As String, _
sSeries1 As String, _
Optional sSeries2 As String = "", _
Optional sCtrl As String = "Control")
'Takes inbound parameters and puts them into the Control Tab, which will be used for
'Advanced Filters
With ThisWorkbook.Sheets(sCtrl)
.Range(sCritRange) = sSeries1
.Range(sCritRange).Offset(0, 1) = sSeries2
End With
End Sub
Public Function Criteria_Find( _
sChartSheet As String, _
Optional sControlSheet As String = "Control") As String
Dim rEvalCell As Range
'========================================================================== ================
'=================Searches in Control Tab for the name of the worksheet containing=========
'=================the chart that was clicked. Returns the address of the 1st criteria cell
'========================================================================== ================
'Finds the cell that contains the Chart Sheets name
Set rEvalCell = Worksheets(sControlSheet).Cells.Find(sChartSheet, , xlValues, xlWhole)
Criteria_Find = rEvalCell.Offset(2, 1).Address
End Function
I am thinking that the find function and active cell argument being left out may influence it. Or maybe my objects are getting polluted with the data type conversions, but these are only guesses. Anyone ever experience this, or know what could be going wrong?
I am working on a project and want to make things happen (Advanced Filter) when a user clicks on a series in an Embedded chart.
I set up the basic selection framework with test code, and everything worked fine. I then made some revisions to make my code more flexible (been reading some OOP books) and now my selection behavior is erratic.
Selecting the chart, series, data points, etc is now all over the place. Sometimes after clicking in the chart it just jumps back to a cell in the worksheet. In spite of this, the code executes as expected when I can get the selection right.
Here is the class module code, which is basically the only thing acting on these charts.
SELECTION PROCEDURE
Public WithEvents myChartClass As Chart
Private Sub myChartClass_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
Dim wsChartSheet As Worksheet
Dim varValues As Variant
Dim sArg1 As String, sArg2 As String
Dim sCritCell As String
Set wsChartSheet = myChartClass.Application.ActiveSheet 'Used to log the worksheet which
'contains the clicked chart
Select Case ElementID
Case xlSeries 'Check that selection is a Chart series
sArg1 = ActiveChart.SeriesCollection(Arg1).Name 'Get the name of the Series
If Arg2 > 0 Then 'If selection is refined to a data point, expand my output
varValues = ActiveChart.SeriesCollection(Arg1).XValues
sArg2 = varValues(Arg2) 'Get the name of narrowed datapoint
sCritCell = Criteria_Find(wsChartSheet.Name) 'gets the address of the
'First Cell being used for Advanced Filter Criteria
Criteria_Set sCritCell, sArg1, sArg2 'Output the Selections as
'Text to Control Worksheet
ElseIf Arg2 < 0 Then 'If selection not refined to one data point, do less
sCritCell = Criteria_Find(wsChartSheet.Name)
Criteria_Set sCritCell, sArg1
Else
End If
End Select
End Sub
REFERENCED FUNCTIONS
Public Sub Criteria_Set(sCritRange As String, _
sSeries1 As String, _
Optional sSeries2 As String = "", _
Optional sCtrl As String = "Control")
'Takes inbound parameters and puts them into the Control Tab, which will be used for
'Advanced Filters
With ThisWorkbook.Sheets(sCtrl)
.Range(sCritRange) = sSeries1
.Range(sCritRange).Offset(0, 1) = sSeries2
End With
End Sub
Public Function Criteria_Find( _
sChartSheet As String, _
Optional sControlSheet As String = "Control") As String
Dim rEvalCell As Range
'========================================================================== ================
'=================Searches in Control Tab for the name of the worksheet containing=========
'=================the chart that was clicked. Returns the address of the 1st criteria cell
'========================================================================== ================
'Finds the cell that contains the Chart Sheets name
Set rEvalCell = Worksheets(sControlSheet).Cells.Find(sChartSheet, , xlValues, xlWhole)
Criteria_Find = rEvalCell.Offset(2, 1).Address
End Function
I am thinking that the find function and active cell argument being left out may influence it. Or maybe my objects are getting polluted with the data type conversions, but these are only guesses. Anyone ever experience this, or know what could be going wrong?