PDA

View Full Version : Embedded Chart Events - Selection Object Erratic



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?

mantooth29
11-23-2012, 07:22 PM
Well there is not much progress, but I have isolated the bug to this function..

Public Function Criteria_Find( _
sChartSheet As String, _
Optional sControlSheet As String = "Control") As String
Dim rEvalCell As Range

Set rEvalCell = Worksheets(sControlSheet).Cells.Find(sChartSheet, , xlValues, xlWhole)
Criteria_Find = rEvalCell.Offset(2, 1).Address
End Function

I tried to convert the output to a range, and it is slightly less erratic, but still not as smooth as a chart with no code. I think now that this is very much tied to using the find method on another sheet. I don't think the selection object can maintain itself well while this other stuff is going on.

Just a theory, but I will post back with a solution if I can find one...