I am having where my global variables are being reset when a
subroutine processes/creates OleObject checkboxes. The check boxes
are on the worksheet and are used to turn on and off data points.

I also have a chart where I use with mouse events to select datapoint
off the graph. I have defined an EventsClassModule (since I create
the chart on the fly) and I initialize it and it works fine until
another routine is excecuted where I process data, add checkboxes
(OleObject) and create new charts.

If I comment out the call to the addCheckBox routine, the golbal
variables and the eventsClassModule work fine.

Any suggestions on how to get around this problem?

Thanks in advance for the help.

Rock


Here is the routine to create the checkboxes:

Private Sub addCheckBox(rw As Long, cl As Long)
Dim myRng As Range, myCell As Range, myObj As Object

With adjSH
Set myRng = .Cells(rw, cl)
For Each myCell In myRng.Cells
Set myObj = ActiveSheet.CheckBox.Add
'Left:=myCell.Left + 0.5 * myCell.Width - 0.5
* myCell.Height, _
'Top:=myCell.Top, Width:=myCell.Height,
Height:=myCell.Height)
myObj.Name = "chkbox" & myCell.Row
myObj.Object.Value = True
myObj.Object.Caption = ""
myObj.Object.GroupName = "checkboxes"
myObj.Placement = xlFreeFloating
Next myCell
End With
Set myObj = Nothing

End Sub

Hi Rock,

This is a known bug. My suggestion would be to store the values required
to recreated your global variables on a worksheet right before you start
adding OLE objects and then restore the variables from the worksheet data
when you're done.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Rock" wrote in message
om...
I am having where my global variables are being reset when a
subroutine processes/creates OleObject checkboxes. The check boxes
are on the worksheet and are used to turn on and off data points.

I also have a chart where I use with mouse events to select datapoint
off the graph. I have defined an EventsClassModule (since I create
the chart on the fly) and I initialize it and it works fine until
another routine is excecuted where I process data, add checkboxes
(OleObject) and create new charts.

If I comment out the call to the addCheckBox routine, the golbal
variables and the eventsClassModule work fine.

Any suggestions on how to get around this problem?

Thanks in advance for the help.

Rock


Here is the routine to create the checkboxes:

Private Sub addCheckBox(rw As Long, cl As Long)
Dim myRng As Range, myCell As Range, myObj As Object

With adjSH
Set myRng = .Cells(rw, cl)
For Each myCell In myRng.Cells
Set myObj = ActiveSheet.CheckBox.Add
'Left:=myCell.Left + 0.5 * myCell.Width - 0.5
* myCell.Height, _
'Top:=myCell.Top, Width:=myCell.Height,
Height:=myCell.Height)
myObj.Name = "chkbox" & myCell.Row
myObj.Object.Value = True
myObj.Object.Caption = ""
myObj.Object.GroupName = "checkboxes"
myObj.Placement = xlFreeFloating
Next myCell
End With
Set myObj = Nothing

End Sub

I have an initialize function (call init) that I call before and after the
posted routine. Here is a list of my global variables and the init()
routine which calls the InitializeChart for my chart event class. Also
listed is the code showing the call to init. In the readAdjustedData
subroutine, the OleOBjects are used.

I don't understand how I can reinitialize my eventclassobject (for the
dsChart object). I can do it with another button, however, I cannot do it
in the readAdjustedDataCommandButton_Click() sub. The Public Const dsPlot
As String = "DSPlot.ch"
variable doesn't get wiped out, but I am unable to Set dsChart =
Charts(dsPlot). dsChart never gets reinitialized even though the dsPlot
variable is correct.

I am not sure how to get around this except to have another button to push
and re-initialize after the readAdjustedDataCommandButton_Click() sub is
completed.

Any Suggestions?

Thanks,

Rock

PS.. Thanks for the help Rob


' Module Scope Variables
Public myClassModule As New EventClassModule
Public tdSH As Object, digSH As Object, rdSH As Object, adjSH As Object,
ssSH As Object, plSH As Object

' Define Constants
Public Const testSheet As String = "TestSummary"
Public Const digitizeSheet As String = "Digitize"
Public Const rawDataSheet As String = "RawTraceData"
Public Const adjDataSheet As String = "AdjustedTraceData"
Public Const summStatsSheet As String = "SummaryStats"
Public Const plotSheet As String = "PlotData"
Public Const dsPlot As String = "DSPlot.ch"

Public Sub init()
Dim i As Integer
Set tdSH = Worksheets(testSheet)
Set digSH = Worksheets(digitizeSheet)
Set rdSH = Worksheets(rawDataSheet)
Set adjSH = Worksheets(adjDataSheet)
Set ssSH = Worksheets(summStatsSheet)
Set plSH = Worksheets(plotSheet)
Set dsChart = Nothing
On Error Resume Next
Set dsChart = Charts(dsPlot)
Call InitializeChart
End Sub

Public Sub InitializeChart()
Set myClassModule.myChartClass = Nothing
Set myClassModule.myChartClass = dsChart
End Sub

Private Sub readAdjustedDataCommandButton_Click()
Application.ScreenUpdating = False
Call init
Call ClearAllData
Call readAdjustedData
adjSH.Activate
Range("B12").Select
Call init
Application.ScreenUpdating = True
End Sub

"Rob Bovey" wrote in message
...
Hi Rock,

This is a known bug. My suggestion would be to store the values
required
to recreated your global variables on a worksheet right before you start
adding OLE objects and then restore the variables from the worksheet data
when you're done.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Rock" wrote in message
om...
I am having where my global variables are being reset when a
subroutine processes/creates OleObject checkboxes. The check boxes
are on the worksheet and are used to turn on and off data points.

I also have a chart where I use with mouse events to select datapoint
off the graph. I have defined an EventsClassModule (since I create
the chart on the fly) and I initialize it and it works fine until
another routine is excecuted where I process data, add checkboxes
(OleObject) and create new charts.

If I comment out the call to the addCheckBox routine, the golbal
variables and the eventsClassModule work fine.

Any suggestions on how to get around this problem?

Thanks in advance for the help.

Rock


Here is the routine to create the checkboxes:

Private Sub addCheckBox(rw As Long, cl As Long)
Dim myRng As Range, myCell As Range, myObj As Object

With adjSH
Set myRng = .Cells(rw, cl)
For Each myCell In myRng.Cells
Set myObj = ActiveSheet.CheckBox.Add
'Left:=myCell.Left + 0.5 * myCell.Width - 0.5
* myCell.Height, _
'Top:=myCell.Top, Width:=myCell.Height,
Height:=myCell.Height)
myObj.Name = "chkbox" & myCell.Row
myObj.Object.Value = True
myObj.Object.Caption = ""
myObj.Object.GroupName = "checkboxes"
myObj.Placement = xlFreeFloating
Next myCell
End With
Set myObj = Nothing

End Sub

Hi Rock,

What exactly does "DSPlot.ds" refer to? The only way it's valid in the
init procedure below is if it is the sheet tab name of a Chart sheet (not a
chart located on a worksheet) in the same workbook where the init procedure
is currently running. I've tested this scenario here and it seems to work OK
for me.

The first thing I'd recommend doing is getting rid of the On Error
Resume Next statement in your init procedure. That way you'll get an
immediate run-time error on the Set dsChart = Charts(dsPlot) line that ought
to shed more light on exactly what the problem is.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Mr.RockDoc" wrote in message
...
I have an initialize function (call init) that I call before and after
the
posted routine. Here is a list of my global variables and the init()
routine which calls the InitializeChart for my chart event class. Also
listed is the code showing the call to init. In the readAdjustedData
subroutine, the OleOBjects are used.

I don't understand how I can reinitialize my eventclassobject (for the
dsChart object). I can do it with another button, however, I cannot do it
in the readAdjustedDataCommandButton_Click() sub. The Public Const dsPlot
As String = "DSPlot.ch"
variable doesn't get wiped out, but I am unable to Set dsChart =
Charts(dsPlot). dsChart never gets reinitialized even though the dsPlot
variable is correct.

I am not sure how to get around this except to have another button to push
and re-initialize after the readAdjustedDataCommandButton_Click() sub is
completed.

Any Suggestions?

Thanks,

Rock

PS.. Thanks for the help Rob


' Module Scope Variables
Public myClassModule As New EventClassModule
Public tdSH As Object, digSH As Object, rdSH As Object, adjSH As Object,
ssSH As Object, plSH As Object

' Define Constants
Public Const testSheet As String = "TestSummary"
Public Const digitizeSheet As String = "Digitize"
Public Const rawDataSheet As String = "RawTraceData"
Public Const adjDataSheet As String = "AdjustedTraceData"
Public Const summStatsSheet As String = "SummaryStats"
Public Const plotSheet As String = "PlotData"
Public Const dsPlot As String = "DSPlot.ch"

Public Sub init()
Dim i As Integer
Set tdSH = Worksheets(testSheet)
Set digSH = Worksheets(digitizeSheet)
Set rdSH = Worksheets(rawDataSheet)
Set adjSH = Worksheets(adjDataSheet)
Set ssSH = Worksheets(summStatsSheet)
Set plSH = Worksheets(plotSheet)
Set dsChart = Nothing
On Error Resume Next
Set dsChart = Charts(dsPlot)
Call InitializeChart
End Sub

Public Sub InitializeChart()
Set myClassModule.myChartClass = Nothing
Set myClassModule.myChartClass = dsChart
End Sub

Private Sub readAdjustedDataCommandButton_Click()
Application.ScreenUpdating = False
Call init
Call ClearAllData
Call readAdjustedData
adjSH.Activate
Range("B12").Select
Call init
Application.ScreenUpdating = True
End Sub

"Rob Bovey" wrote in message
...
Hi Rock,

This is a known bug. My suggestion would be to store the values
required
to recreated your global variables on a worksheet right before you start
adding OLE objects and then restore the variables from the worksheet
data
when you're done.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Rock" wrote in message
om...
I am having where my global variables are being reset when a
subroutine processes/creates OleObject checkboxes. The check boxes
are on the worksheet and are used to turn on and off data points.

I also have a chart where I use with mouse events to select datapoint
off the graph. I have defined an EventsClassModule (since I create
the chart on the fly) and I initialize it and it works fine until
another routine is excecuted where I process data, add checkboxes
(OleObject) and create new charts.

If I comment out the call to the addCheckBox routine, the golbal
variables and the eventsClassModule work fine.

Any suggestions on how to get around this problem?

Thanks in advance for the help.

Rock


Here is the routine to create the checkboxes:

Private Sub addCheckBox(rw As Long, cl As Long)
Dim myRng As Range, myCell As Range, myObj As Object

With adjSH
Set myRng = .Cells(rw, cl)
For Each myCell In myRng.Cells
Set myObj = ActiveSheet.CheckBox.Add
'Left:=myCell.Left + 0.5 * myCell.Width - 0.5
* myCell.Height, _
'Top:=myCell.Top, Width:=myCell.Height,
Height:=myCell.Height)
myObj.Name = "chkbox" & myCell.Row
myObj.Object.Value = True
myObj.Object.Caption = ""
myObj.Object.GroupName = "checkboxes"
myObj.Placement = xlFreeFloating
Next myCell
End With
Set myObj = Nothing

End Sub

Rob,

"DSPlot.ch" refers to a chart sheet that is created using VBA. It is also
the chart that I need to use the eventclassmodule. Since the chart is
created using VBA, I don't have any code on the chart sheet, but I need to
click on the chart data series and use the selected points in VBA. I have
removed the "On Error Resume Next" which I used as I was messing around with
the code. The code included in this thread doesn't need the resume next,
since no errors are created using the Set dsChart = Charts(dsPlot). I have
put a debug.print statement before and after the Set command and looked at
the dsPlot string variable. The dsPlot variable never loses it's string
value; however, dsChart never gets set to the chart object and is always
equal to Nothing when the routine uses OleObjects. If I comment out the
OleObjects call, the dsChart is set properly every time.

Rock

"Rob Bovey" wrote in message
...
Hi Rock,

What exactly does "DSPlot.ds" refer to? The only way it's valid in the
init procedure below is if it is the sheet tab name of a Chart sheet (not
a
chart located on a worksheet) in the same workbook where the init
procedure
is currently running. I've tested this scenario here and it seems to work
OK
for me.

The first thing I'd recommend doing is getting rid of the On Error
Resume Next statement in your init procedure. That way you'll get an
immediate run-time error on the Set dsChart = Charts(dsPlot) line that
ought
to shed more light on exactly what the problem is.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Mr.RockDoc" wrote in message
...
I have an initialize function (call init) that I call before and after
the
posted routine. Here is a list of my global variables and the init()
routine which calls the InitializeChart for my chart event class. Also
listed is the code showing the call to init. In the readAdjustedData
subroutine, the OleOBjects are used.

I don't understand how I can reinitialize my eventclassobject (for the
dsChart object). I can do it with another button, however, I cannot do
it
in the readAdjustedDataCommandButton_Click() sub. The Public Const
dsPlot
As String = "DSPlot.ch"
variable doesn't get wiped out, but I am unable to Set dsChart =
Charts(dsPlot). dsChart never gets reinitialized even though the dsPlot
variable is correct.

I am not sure how to get around this except to have another button to
push
and re-initialize after the readAdjustedDataCommandButton_Click() sub is
completed.

Any Suggestions?

Thanks,

Rock

PS.. Thanks for the help Rob


' Module Scope Variables
Public myClassModule As New EventClassModule
Public tdSH As Object, digSH As Object, rdSH As Object, adjSH As Object,
ssSH As Object, plSH As Object

' Define Constants
Public Const testSheet As String = "TestSummary"
Public Const digitizeSheet As String = "Digitize"
Public Const rawDataSheet As String = "RawTraceData"
Public Const adjDataSheet As String = "AdjustedTraceData"
Public Const summStatsSheet As String = "SummaryStats"
Public Const plotSheet As String = "PlotData"
Public Const dsPlot As String = "DSPlot.ch"

Public Sub init()
Dim i As Integer
Set tdSH = Worksheets(testSheet)
Set digSH = Worksheets(digitizeSheet)
Set rdSH = Worksheets(rawDataSheet)
Set adjSH = Worksheets(adjDataSheet)
Set ssSH = Worksheets(summStatsSheet)
Set plSH = Worksheets(plotSheet)
Set dsChart = Nothing
On Error Resume Next
Set dsChart = Charts(dsPlot)
Call InitializeChart
End Sub

Public Sub InitializeChart()
Set myClassModule.myChartClass = Nothing
Set myClassModule.myChartClass = dsChart
End Sub

Private Sub readAdjustedDataCommandButton_Click()
Application.ScreenUpdating = False
Call init
Call ClearAllData
Call readAdjustedData
adjSH.Activate
Range("B12").Select
Call init
Application.ScreenUpdating = True
End Sub

"Rob Bovey" wrote in message
...
Hi Rock,

This is a known bug. My suggestion would be to store the values
required
to recreated your global variables on a worksheet right before you
start
adding OLE objects and then restore the variables from the worksheet
data
when you're done.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Rock" wrote in message
om...
I am having where my global variables are being reset when a
subroutine processes/creates OleObject checkboxes. The check boxes
are on the worksheet and are used to turn on and off data points.

I also have a chart where I use with mouse events to select
datapoint
off the graph. I have defined an EventsClassModule (since I create
the chart on the fly) and I initialize it and it works fine until
another routine is excecuted where I process data, add checkboxes
(OleObject) and create new charts.

If I comment out the call to the addCheckBox routine, the golbal
variables and the eventsClassModule work fine.

Any suggestions on how to get around this problem?

Thanks in advance for the help.

Rock


Here is the routine to create the checkboxes:

Private Sub addCheckBox(rw As Long, cl As Long)
Dim myRng As Range, myCell As Range, myObj As Object

With adjSH
Set myRng = .Cells(rw, cl)
For Each myCell In myRng.Cells
Set myObj = ActiveSheet.CheckBox.Add
'Left:=myCell.Left + 0.5 * myCell.Width -
0.5
* myCell.Height, _
'Top:=myCell.Top, Width:=myCell.Height,
Height:=myCell.Height)
myObj.Name = "chkbox" & myCell.Row
myObj.Object.Value = True
myObj.Object.Caption = ""
myObj.Object.GroupName = "checkboxes"
myObj.Placement = xlFreeFloating
Next myCell
End With
Set myObj = Nothing

End Sub

Hi Rock,

I'm a bit confused on the sequence of events. When global variables get
reset as a result of adding OleObjects to a worksheet, the high-level
picture should look something like this:

Initialize global variables
Other code
Create OleObjects on a Worksheet (global variables are now dead)
Re-initialize global variables
Other code

It sounds like you're trying to use one of the global variables after it
has been killed by the OleObject routine but before it's been reinitialized.
Make absolutely sure that you are running your init procedure immediately
after you've finished creating any OleObjects on Worksheets, before any
other code gets run.

If you're doing this and still having problems, modify your init
procedure as shown below and see what comes out in the Immediate window:

Public Sub init()
Dim i As Integer
''' Code in between not shown....
Set dsChart = Charts(dsPlot)
Debug.Print dsChart.Name
Set myClassModule.myChartClass = dsChart
Debug.Print myClassModule.myChartClass.Name
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Mr.RockDoc" wrote in message
...
Rob,

"DSPlot.ch" refers to a chart sheet that is created using VBA. It is also
the chart that I need to use the eventclassmodule. Since the chart is
created using VBA, I don't have any code on the chart sheet, but I need to
click on the chart data series and use the selected points in VBA. I have
removed the "On Error Resume Next" which I used as I was messing around
with
the code. The code included in this thread doesn't need the resume next,
since no errors are created using the Set dsChart = Charts(dsPlot). I
have
put a debug.print statement before and after the Set command and looked at
the dsPlot string variable. The dsPlot variable never loses it's string
value; however, dsChart never gets set to the chart object and is always
equal to Nothing when the routine uses OleObjects. If I comment out the
OleObjects call, the dsChart is set properly every time.

Rock

"Rob Bovey" wrote in message
...
Hi Rock,

What exactly does "DSPlot.ds" refer to? The only way it's valid in
the
init procedure below is if it is the sheet tab name of a Chart sheet
(not
a
chart located on a worksheet) in the same workbook where the init
procedure
is currently running. I've tested this scenario here and it seems to
work
OK
for me.

The first thing I'd recommend doing is getting rid of the On Error
Resume Next statement in your init procedure. That way you'll get an
immediate run-time error on the Set dsChart = Charts(dsPlot) line that
ought
to shed more light on exactly what the problem is.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Mr.RockDoc" wrote in message
...
I have an initialize function (call init) that I call before and
after
the
posted routine. Here is a list of my global variables and the init()
routine which calls the InitializeChart for my chart event class.
Also
listed is the code showing the call to init. In the readAdjustedData
subroutine, the OleOBjects are used.

I don't understand how I can reinitialize my eventclassobject (for the
dsChart object). I can do it with another button, however, I cannot
do
it
in the readAdjustedDataCommandButton_Click() sub. The Public Const
dsPlot
As String = "DSPlot.ch"
variable doesn't get wiped out, but I am unable to Set dsChart =
Charts(dsPlot). dsChart never gets reinitialized even though the
dsPlot
variable is correct.

I am not sure how to get around this except to have another button to
push
and re-initialize after the readAdjustedDataCommandButton_Click() sub
is
completed.

Any Suggestions?

Thanks,

Rock

PS.. Thanks for the help Rob


' Module Scope Variables
Public myClassModule As New EventClassModule
Public tdSH As Object, digSH As Object, rdSH As Object, adjSH As
Object,
ssSH As Object, plSH As Object

' Define Constants
Public Const testSheet As String = "TestSummary"
Public Const digitizeSheet As String = "Digitize"
Public Const rawDataSheet As String = "RawTraceData"
Public Const adjDataSheet As String = "AdjustedTraceData"
Public Const summStatsSheet As String = "SummaryStats"
Public Const plotSheet As String = "PlotData"
Public Const dsPlot As String = "DSPlot.ch"

Public Sub init()
Dim i As Integer
Set tdSH = Worksheets(testSheet)
Set digSH = Worksheets(digitizeSheet)
Set rdSH = Worksheets(rawDataSheet)
Set adjSH = Worksheets(adjDataSheet)
Set ssSH = Worksheets(summStatsSheet)
Set plSH = Worksheets(plotSheet)
Set dsChart = Nothing
On Error Resume Next
Set dsChart = Charts(dsPlot)
Call InitializeChart
End Sub

Public Sub InitializeChart()
Set myClassModule.myChartClass = Nothing
Set myClassModule.myChartClass = dsChart
End Sub

Private Sub readAdjustedDataCommandButton_Click()
Application.ScreenUpdating = False
Call init
Call ClearAllData
Call readAdjustedData
adjSH.Activate
Range("B12").Select
Call init
Application.ScreenUpdating = True
End Sub

"Rob Bovey" wrote in message
...
Hi Rock,

This is a known bug. My suggestion would be to store the values
required
to recreated your global variables on a worksheet right before you
start
adding OLE objects and then restore the variables from the worksheet
data
when you're done.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Rock" wrote in message
om...
I am having where my global variables are being reset when a
subroutine processes/creates OleObject checkboxes. The check
boxes
are on the worksheet and are used to turn on and off data points.

I also have a chart where I use with mouse events to select
datapoint
off the graph. I have defined an EventsClassModule (since I
create
the chart on the fly) and I initialize it and it works fine until
another routine is excecuted where I process data, add checkboxes
(OleObject) and create new charts.

If I comment out the call to the addCheckBox routine, the golbal
variables and the eventsClassModule work fine.

Any suggestions on how to get around this problem?

Thanks in advance for the help.

Rock


Here is the routine to create the checkboxes:

Private Sub addCheckBox(rw As Long, cl As Long)
Dim myRng As Range, myCell As Range, myObj As Object

With adjSH
Set myRng = .Cells(rw, cl)
For Each myCell In myRng.Cells
Set myObj = ActiveSheet.CheckBox.Add
'Left:=myCell.Left + 0.5 * myCell.Width -
0.5
* myCell.Height, _
'Top:=myCell.Top, Width:=myCell.Height,
Height:=myCell.Height)
myObj.Name = "chkbox" & myCell.Row
myObj.Object.Value = True
myObj.Object.Caption = ""
myObj.Object.GroupName = "checkboxes"
myObj.Placement = xlFreeFloating
Next myCell
End With
Set myObj = Nothing

End Sub

Rob, This is exactly the process that I am using for initialization. When
I put the debug statements in the init routine, they all print out the same
name which is "DSPlot.ch" which is correct. However, in looking at the
dsPlot object, it shows nothing and therefore the eventclassmodule doesn't
work when I click on the "DSPlot.ch"

very strange....

Rock


"Rob Bovey" wrote in message
...
Hi Rock,

I'm a bit confused on the sequence of events. When global variables
get
reset as a result of adding OleObjects to a worksheet, the high-level
picture should look something like this:

Initialize global variables
Other code
Create OleObjects on a Worksheet (global variables are now dead)
Re-initialize global variables
Other code

It sounds like you're trying to use one of the global variables after
it
has been killed by the OleObject routine but before it's been
reinitialized.
Make absolutely sure that you are running your init procedure immediately
after you've finished creating any OleObjects on Worksheets, before any
other code gets run.

If you're doing this and still having problems, modify your init
procedure as shown below and see what comes out in the Immediate window:

Public Sub init()
Dim i As Integer
''' Code in between not shown....
Set dsChart = Charts(dsPlot)
Debug.Print dsChart.Name
Set myClassModule.myChartClass = dsChart
Debug.Print myClassModule.myChartClass.Name
End Sub


--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Mr.RockDoc" wrote in message
...
Rob,

"DSPlot.ch" refers to a chart sheet that is created using VBA. It is
also
the chart that I need to use the eventclassmodule. Since the chart is
created using VBA, I don't have any code on the chart sheet, but I need
to
click on the chart data series and use the selected points in VBA. I
have
removed the "On Error Resume Next" which I used as I was messing around
with
the code. The code included in this thread doesn't need the resume
next,
since no errors are created using the Set dsChart = Charts(dsPlot). I
have
put a debug.print statement before and after the Set command and looked
at
the dsPlot string variable. The dsPlot variable never loses it's string
value; however, dsChart never gets set to the chart object and is always
equal to Nothing when the routine uses OleObjects. If I comment out the
OleObjects call, the dsChart is set properly every time.

Rock

"Rob Bovey" wrote in message
...
Hi Rock,

What exactly does "DSPlot.ds" refer to? The only way it's valid in
the
init procedure below is if it is the sheet tab name of a Chart sheet
(not
a
chart located on a worksheet) in the same workbook where the init
procedure
is currently running. I've tested this scenario here and it seems to
work
OK
for me.

The first thing I'd recommend doing is getting rid of the On Error
Resume Next statement in your init procedure. That way you'll get an
immediate run-time error on the Set dsChart = Charts(dsPlot) line that
ought
to shed more light on exactly what the problem is.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Mr.RockDoc" wrote in message
...
I have an initialize function (call init) that I call before and
after
the
posted routine. Here is a list of my global variables and the
init()
routine which calls the InitializeChart for my chart event class.
Also
listed is the code showing the call to init. In the
readAdjustedData
subroutine, the OleOBjects are used.

I don't understand how I can reinitialize my eventclassobject (for
the
dsChart object). I can do it with another button, however, I cannot
do
it
in the readAdjustedDataCommandButton_Click() sub. The Public Const
dsPlot
As String = "DSPlot.ch"
variable doesn't get wiped out, but I am unable to Set dsChart =
Charts(dsPlot). dsChart never gets reinitialized even though the
dsPlot
variable is correct.

I am not sure how to get around this except to have another button
to
push
and re-initialize after the readAdjustedDataCommandButton_Click()
sub
is
completed.

Any Suggestions?

Thanks,

Rock

PS.. Thanks for the help Rob


' Module Scope Variables
Public myClassModule As New EventClassModule
Public tdSH As Object, digSH As Object, rdSH As Object, adjSH As
Object,
ssSH As Object, plSH As Object

' Define Constants
Public Const testSheet As String = "TestSummary"
Public Const digitizeSheet As String = "Digitize"
Public Const rawDataSheet As String = "RawTraceData"
Public Const adjDataSheet As String = "AdjustedTraceData"
Public Const summStatsSheet As String = "SummaryStats"
Public Const plotSheet As String = "PlotData"
Public Const dsPlot As String = "DSPlot.ch"

Public Sub init()
Dim i As Integer
Set tdSH = Worksheets(testSheet)
Set digSH = Worksheets(digitizeSheet)
Set rdSH = Worksheets(rawDataSheet)
Set adjSH = Worksheets(adjDataSheet)
Set ssSH = Worksheets(summStatsSheet)
Set plSH = Worksheets(plotSheet)
Set dsChart = Nothing
On Error Resume Next
Set dsChart = Charts(dsPlot)
Call InitializeChart
End Sub

Public Sub InitializeChart()
Set myClassModule.myChartClass = Nothing
Set myClassModule.myChartClass = dsChart
End Sub

Private Sub readAdjustedDataCommandButton_Click()
Application.ScreenUpdating = False
Call init
Call ClearAllData
Call readAdjustedData
adjSH.Activate
Range("B12").Select
Call init
Application.ScreenUpdating = True
End Sub

"Rob Bovey" wrote in message
...
Hi Rock,

This is a known bug. My suggestion would be to store the
values
required
to recreated your global variables on a worksheet right before you
start
adding OLE objects and then restore the variables from the
worksheet
data
when you're done.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Rock" wrote in message
om...
I am having where my global variables are being reset when a
subroutine processes/creates OleObject checkboxes. The check
boxes
are on the worksheet and are used to turn on and off data
points.

I also have a chart where I use with mouse events to select
datapoint
off the graph. I have defined an EventsClassModule (since I
create
the chart on the fly) and I initialize it and it works fine
until
another routine is excecuted where I process data, add
checkboxes
(OleObject) and create new charts.

If I comment out the call to the addCheckBox routine, the golbal
variables and the eventsClassModule work fine.

Any suggestions on how to get around this problem?

Thanks in advance for the help.

Rock


Here is the routine to create the checkboxes:

Private Sub addCheckBox(rw As Long, cl As Long)
Dim myRng As Range, myCell As Range, myObj As Object

With adjSH
Set myRng = .Cells(rw, cl)
For Each myCell In myRng.Cells
Set myObj = ActiveSheet.CheckBox.Add
'Left:=myCell.Left + 0.5 *
myCell.Width -
0.5
* myCell.Height, _
'Top:=myCell.Top, Width:=myCell.Height,
Height:=myCell.Height)
myObj.Name = "chkbox" & myCell.Row
myObj.Object.Value = True
myObj.Object.Caption = ""
myObj.Object.GroupName = "checkboxes"
myObj.Placement = xlFreeFloating
Next myCell
End With
Set myObj = Nothing

End Sub

"Mr.RockDoc" wrote in message
...
Rob, This is exactly the process that I am using for initialization.
When
I put the debug statements in the init routine, they all print out the
same
name which is "DSPlot.ch" which is correct. However, in looking at the
dsPlot object, it shows nothing and therefore the eventclassmodule doesn't
work when I click on the "DSPlot.ch"

Hi Rock,

In the following chunk of code from the init procedu

Set dsChart = Charts(dsPlot)
Debug.Print dsChart.Name
Set myClassModule.myChartClass = dsChart
Debug.Print myClassModule.myChartClass.Name

dsPlot is a String constant. It will not lose its value under any
circumstances. You say that both of the Debug.Print statements print the
same name. This means at the point when the initi procedure was run, the
dsChart object variable and the event handler were both set correctly.

Then you say your event handler doesn't work when you click on the
chart. That tells me that at some point between when the init procedure was
run and when you clicked on the chart you must have run code somewhere that
wiped out the public variables again (maybe in an event procedure?)

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Rob,

Thanks for your patience in trying to help me. I have a 48k excel file that
I can send that illustrates the problem. Email me if you would like it. To
summaryize, I have data in sheet1 a1:b4. I have a chart sheet named
"DSPlot.ch". I have code on sheet1, a PublicDeclaration module and a
EventClassModule. The code on each sheet is listed below.

1) I can click the init button and the chart events work. 2) I click the
checkBoxes command button (which calls init before and after execution) and
the chart events don't work. 3) I click the init button and the chart
events now work.

How can I get the chart events to work without pressing the init button
after using the checkBoxes button?

I hope this is clear.

Rock

'Sheet1 code:
Option Explicit
Private Sub initCommandButton_Click()
Call init
End Sub
Public Sub checkBoxesCommandButton_Click()
Dim rw As Integer, cl As Integer, i As Integer

Call init

' Clear Checkboxes if they exist
Call ClearCheckBoxes

' Add Checkboxes and turn all points On
i = 1
While ActiveSheet.Cells(i, 1) < ""
Call addCheckBox(i, 3)
i = i + 1
Wend
Call init
End Sub

Private Sub addCheckBox(rw As Integer, cl As Integer)
Dim myRng As Range, myCell As Range, myObj As OLEObject

With ActiveSheet
Set myRng = .Cells(rw, cl)
For Each myCell In myRng.Cells
Set myObj = .OLEObjects.Add(ClassType:="Forms.Checkbox.1", _
Link:=False, DisplayAsIcon:=False, _
Left:=myCell.Left + 0.5 * myCell.Width - 0.5 *
myCell.Height, _
Top:=myCell.Top, Width:=myCell.Height,
Height:=myCell.Height)
myObj.Name = "chkbox" & myCell.Row
myObj.Object.Value = True
myObj.Object.Caption = ""
myObj.Object.GroupName = "checkboxes"
myObj.Placement = xlFreeFloating
Next myCell
End With
Set myObj = Nothing

End Sub

Private Sub ClearCheckBoxes()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Then
obj.Delete
End If
Next
End Sub

'PublicDeclarations Module:
Option Explicit

Public myClassModule As New EventClassModule
Public Const dsPlot As String = "DSPlot.ch"
Public dsChart As Chart

Public Sub init()
Set dsChart = Charts(dsPlot)
Debug.Print dsChart.Name
Set myClassModule.myChartClass = dsChart
Debug.Print myClassModule.myChartClass.Name
End Sub

'EventsClassModule:
Option Explicit
Public WithEvents myChartClass As Chart

Private Sub myChartClass_Select(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long)

Dim ser As Series, xPt As Double, yPt As Double
Application.ScreenUpdating = False
Select Case ElementID
Case xlSeries
' Arg1 reports the index of the selected series in the
' series collection
Set ser = ActiveChart.SeriesCollection(Arg1)
' Arg2 reports -1 if the whole series was selected,
' otherwise it reports the index of the point selected
' in the series
If Arg2 = -1 Then
MsgBox "Click again to refine your selection to a point."
Else
MsgBox "(X,Y) = " & "(" & ser.XValues(Arg2) & "," &
ser.Values(Arg2) & ")"

End If
End Select
End Sub

"Rob Bovey" wrote in message
...
"Mr.RockDoc" wrote in message
...
Rob, This is exactly the process that I am using for initialization.
When
I put the debug statements in the init routine, they all print out the
same
name which is "DSPlot.ch" which is correct. However, in looking at the
dsPlot object, it shows nothing and therefore the eventclassmodule
doesn't
work when I click on the "DSPlot.ch"

Hi Rock,

In the following chunk of code from the init procedu

Set dsChart = Charts(dsPlot)
Debug.Print dsChart.Name
Set myClassModule.myChartClass = dsChart
Debug.Print myClassModule.myChartClass.Name

dsPlot is a String constant. It will not lose its value under any
circumstances. You say that both of the Debug.Print statements print the
same name. This means at the point when the initi procedure was run, the
dsChart object variable and the event handler were both set correctly.

Then you say your event handler doesn't work when you click on the
chart. That tells me that at some point between when the init procedure
was
run and when you clicked on the chart you must have run code somewhere
that
wiped out the public variables again (maybe in an event procedure?)

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Hi Rock,

Go ahead and send me your workbook and I'll have a look at it:


--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Mr.RockDoc" wrote in message
...
Rob,

Thanks for your patience in trying to help me. I have a 48k excel file
that
I can send that illustrates the problem. Email me if you would like it.
To
summaryize, I have data in sheet1 a1:b4. I have a chart sheet named
"DSPlot.ch". I have code on sheet1, a PublicDeclaration module and a
EventClassModule. The code on each sheet is listed below.

1) I can click the init button and the chart events work. 2) I click
the
checkBoxes command button (which calls init before and after execution)
and
the chart events don't work. 3) I click the init button and the chart
events now work.

How can I get the chart events to work without pressing the init button
after using the checkBoxes button?

I hope this is clear.

Rock

'Sheet1 code:
Option Explicit
Private Sub initCommandButton_Click()
Call init
End Sub
Public Sub checkBoxesCommandButton_Click()
Dim rw As Integer, cl As Integer, i As Integer

Call init

' Clear Checkboxes if they exist
Call ClearCheckBoxes

' Add Checkboxes and turn all points On
i = 1
While ActiveSheet.Cells(i, 1) < ""
Call addCheckBox(i, 3)
i = i + 1
Wend
Call init
End Sub

Private Sub addCheckBox(rw As Integer, cl As Integer)
Dim myRng As Range, myCell As Range, myObj As OLEObject

With ActiveSheet
Set myRng = .Cells(rw, cl)
For Each myCell In myRng.Cells
Set myObj = .OLEObjects.Add(ClassType:="Forms.Checkbox.1", _
Link:=False, DisplayAsIcon:=False, _
Left:=myCell.Left + 0.5 * myCell.Width - 0.5 *
myCell.Height, _
Top:=myCell.Top, Width:=myCell.Height,
Height:=myCell.Height)
myObj.Name = "chkbox" & myCell.Row
myObj.Object.Value = True
myObj.Object.Caption = ""
myObj.Object.GroupName = "checkboxes"
myObj.Placement = xlFreeFloating
Next myCell
End With
Set myObj = Nothing

End Sub

Private Sub ClearCheckBoxes()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Then
obj.Delete
End If
Next
End Sub

'PublicDeclarations Module:
Option Explicit

Public myClassModule As New EventClassModule
Public Const dsPlot As String = "DSPlot.ch"
Public dsChart As Chart

Public Sub init()
Set dsChart = Charts(dsPlot)
Debug.Print dsChart.Name
Set myClassModule.myChartClass = dsChart
Debug.Print myClassModule.myChartClass.Name
End Sub

'EventsClassModule:
Option Explicit
Public WithEvents myChartClass As Chart

Private Sub myChartClass_Select(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long)

Dim ser As Series, xPt As Double, yPt As Double
Application.ScreenUpdating = False
Select Case ElementID
Case xlSeries
' Arg1 reports the index of the selected series in the
' series collection
Set ser = ActiveChart.SeriesCollection(Arg1)
' Arg2 reports -1 if the whole series was selected,
' otherwise it reports the index of the point selected
' in the series
If Arg2 = -1 Then
MsgBox "Click again to refine your selection to a point."
Else
MsgBox "(X,Y) = " & "(" & ser.XValues(Arg2) & "," &
ser.Values(Arg2) & ")"

End If
End Select
End Sub

"Rob Bovey" wrote in message
...
"Mr.RockDoc" wrote in message
...
Rob, This is exactly the process that I am using for initialization.
When
I put the debug statements in the init routine, they all print out the
same
name which is "DSPlot.ch" which is correct. However, in looking at
the
dsPlot object, it shows nothing and therefore the eventclassmodule
doesn't
work when I click on the "DSPlot.ch"

Hi Rock,

In the following chunk of code from the init procedu

Set dsChart = Charts(dsPlot)
Debug.Print dsChart.Name
Set myClassModule.myChartClass = dsChart
Debug.Print myClassModule.myChartClass.Name

dsPlot is a String constant. It will not lose its value under any
circumstances. You say that both of the Debug.Print statements print the
same name. This means at the point when the initi procedure was run, the
dsChart object variable and the event handler were both set correctly.

Then you say your event handler doesn't work when you click on the
chart. That tells me that at some point between when the init procedure
was
run and when you clicked on the chart you must have run code somewhere
that
wiped out the public variables again (maybe in an event procedure?)

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Rob,

That fixed the problem. Just in case someone else is interested in the
solution the last "Call init" line in the code below was changed to:

Application.OnTime Now(), "init"

As stated by Rob:
"What this does is allow the event procedure to finish executing completely
before the init procedure is run again."
"Everything seems to work fine with this change, although like I said, I
don't know why it would matter. Dynamically adding controls to worksheets
has always had a bit of voodoo associated with it, so I'm just glad when I
can get it working correctly at all. :-)"


Thanks for your help Rob. This has bothered me for some time. What made
you think of this solution?

Many Thanks,

Rock

Public Sub checkBoxesCommandButton_Click()
Dim rw As Integer, cl As Integer, i As Integer

Call init

' Clear Checkboxes if they exist
Call ClearCheckBoxes

' Add Checkboxes and turn all points On
i = 1
While ActiveSheet.Cells(i, 1) < ""
Call addCheckBox(i, 3)
i = i + 1
Wend
Call init
End Sub

"Rob Bovey" wrote in message
...
Hi Rock,

Go ahead and send me your workbook and I'll have a look at it:


--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Mr.RockDoc" wrote in message
...
Rob,

Thanks for your patience in trying to help me. I have a 48k excel file
that
I can send that illustrates the problem. Email me if you would like it.
To
summaryize, I have data in sheet1 a1:b4. I have a chart sheet named
"DSPlot.ch". I have code on sheet1, a PublicDeclaration module and a
EventClassModule. The code on each sheet is listed below.

1) I can click the init button and the chart events work. 2) I click
the
checkBoxes command button (which calls init before and after execution)
and
the chart events don't work. 3) I click the init button and the chart
events now work.

How can I get the chart events to work without pressing the init button
after using the checkBoxes button?

I hope this is clear.

Rock

'Sheet1 code:
Option Explicit
Private Sub initCommandButton_Click()
Call init
End Sub
Public Sub checkBoxesCommandButton_Click()
Dim rw As Integer, cl As Integer, i As Integer

Call init

' Clear Checkboxes if they exist
Call ClearCheckBoxes

' Add Checkboxes and turn all points On
i = 1
While ActiveSheet.Cells(i, 1) < ""
Call addCheckBox(i, 3)
i = i + 1
Wend
Call init
End Sub

Private Sub addCheckBox(rw As Integer, cl As Integer)
Dim myRng As Range, myCell As Range, myObj As OLEObject

With ActiveSheet
Set myRng = .Cells(rw, cl)
For Each myCell In myRng.Cells
Set myObj = .OLEObjects.Add(ClassType:="Forms.Checkbox.1", _
Link:=False, DisplayAsIcon:=False, _
Left:=myCell.Left + 0.5 * myCell.Width - 0.5 *
myCell.Height, _
Top:=myCell.Top, Width:=myCell.Height,
Height:=myCell.Height)
myObj.Name = "chkbox" & myCell.Row
myObj.Object.Value = True
myObj.Object.Caption = ""
myObj.Object.GroupName = "checkboxes"
myObj.Placement = xlFreeFloating
Next myCell
End With
Set myObj = Nothing

End Sub

Private Sub ClearCheckBoxes()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Then
obj.Delete
End If
Next
End Sub

'PublicDeclarations Module:
Option Explicit

Public myClassModule As New EventClassModule
Public Const dsPlot As String = "DSPlot.ch"
Public dsChart As Chart

Public Sub init()
Set dsChart = Charts(dsPlot)
Debug.Print dsChart.Name
Set myClassModule.myChartClass = dsChart
Debug.Print myClassModule.myChartClass.Name
End Sub

'EventsClassModule:
Option Explicit
Public WithEvents myChartClass As Chart

Private Sub myChartClass_Select(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long)

Dim ser As Series, xPt As Double, yPt As Double
Application.ScreenUpdating = False
Select Case ElementID
Case xlSeries
' Arg1 reports the index of the selected series in the
' series collection
Set ser = ActiveChart.SeriesCollection(Arg1)
' Arg2 reports -1 if the whole series was selected,
' otherwise it reports the index of the point selected
' in the series
If Arg2 = -1 Then
MsgBox "Click again to refine your selection to a point."
Else
MsgBox "(X,Y) = " & "(" & ser.XValues(Arg2) & "," &
ser.Values(Arg2) & ")"

End If
End Select
End Sub

"Rob Bovey" wrote in message
...
"Mr.RockDoc" wrote in message
...
Rob, This is exactly the process that I am using for
initialization.
When
I put the debug statements in the init routine, they all print out
the
same
name which is "DSPlot.ch" which is correct. However, in looking at
the
dsPlot object, it shows nothing and therefore the eventclassmodule
doesn't
work when I click on the "DSPlot.ch"

Hi Rock,

In the following chunk of code from the init procedu

Set dsChart = Charts(dsPlot)
Debug.Print dsChart.Name
Set myClassModule.myChartClass = dsChart
Debug.Print myClassModule.myChartClass.Name

dsPlot is a String constant. It will not lose its value under any
circumstances. You say that both of the Debug.Print statements print
the
same name. This means at the point when the initi procedure was run,
the
dsChart object variable and the event handler were both set correctly.

Then you say your event handler doesn't work when you click on the
chart. That tells me that at some point between when the init
procedure
was
run and when you clicked on the chart you must have run code somewhere
that
wiped out the public variables again (maybe in an event procedure?)

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Thanks for your help Rob. This has bothered me for some time. What made
you think of this solution?

Hi Rock,

It's just one of the stock list of things I try when a perfectly good
procedure doesn't do what it looks like it ought to do. Sometimes, and
especially when it comes to event procedures, Excel doesn't really "commit"
the changes (or at least that's my way of thinking about it <g) for some
reason until after the procedure has been allowed to come to a full stop.
The Application.OnTime Now() technique allows you to defer execution of
something else until just after that.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Mr.RockDoc" wrote in message
...
Rob,

That fixed the problem. Just in case someone else is interested in the
solution the last "Call init" line in the code below was changed to:

Application.OnTime Now(), "init"

As stated by Rob:
"What this does is allow the event procedure to finish executing
completely
before the init procedure is run again."
"Everything seems to work fine with this change, although like I said, I
don't know why it would matter. Dynamically adding controls to worksheets
has always had a bit of voodoo associated with it, so I'm just glad when I
can get it working correctly at all. :-)"


Thanks for your help Rob. This has bothered me for some time. What made
you think of this solution?

Many Thanks,

Rock

Public Sub checkBoxesCommandButton_Click()
Dim rw As Integer, cl As Integer, i As Integer

Call init

' Clear Checkboxes if they exist
Call ClearCheckBoxes

' Add Checkboxes and turn all points On
i = 1
While ActiveSheet.Cells(i, 1) < ""
Call addCheckBox(i, 3)
i = i + 1
Wend
Call init
End Sub

"Rob Bovey" wrote in message
...
Hi Rock,

Go ahead and send me your workbook and I'll have a look at it:


--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Mr.RockDoc" wrote in message
...
Rob,

Thanks for your patience in trying to help me. I have a 48k excel
file
that
I can send that illustrates the problem. Email me if you would like
it.
To
summaryize, I have data in sheet1 a1:b4. I have a chart sheet named
"DSPlot.ch". I have code on sheet1, a PublicDeclaration module and a
EventClassModule. The code on each sheet is listed below.

1) I can click the init button and the chart events work. 2) I
click
the
checkBoxes command button (which calls init before and after
execution)
and
the chart events don't work. 3) I click the init button and the
chart
events now work.

How can I get the chart events to work without pressing the init
button
after using the checkBoxes button?

I hope this is clear.

Rock

'Sheet1 code:
Option Explicit
Private Sub initCommandButton_Click()
Call init
End Sub
Public Sub checkBoxesCommandButton_Click()
Dim rw As Integer, cl As Integer, i As Integer

Call init

' Clear Checkboxes if they exist
Call ClearCheckBoxes

' Add Checkboxes and turn all points On
i = 1
While ActiveSheet.Cells(i, 1) < ""
Call addCheckBox(i, 3)
i = i + 1
Wend
Call init
End Sub

Private Sub addCheckBox(rw As Integer, cl As Integer)
Dim myRng As Range, myCell As Range, myObj As OLEObject

With ActiveSheet
Set myRng = .Cells(rw, cl)
For Each myCell In myRng.Cells
Set myObj = .OLEObjects.Add(ClassType:="Forms.Checkbox.1",
_
Link:=False, DisplayAsIcon:=False, _
Left:=myCell.Left + 0.5 * myCell.Width - 0.5 *
myCell.Height, _
Top:=myCell.Top, Width:=myCell.Height,
Height:=myCell.Height)
myObj.Name = "chkbox" & myCell.Row
myObj.Object.Value = True
myObj.Object.Caption = ""
myObj.Object.GroupName = "checkboxes"
myObj.Placement = xlFreeFloating
Next myCell
End With
Set myObj = Nothing

End Sub

Private Sub ClearCheckBoxes()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Then
obj.Delete
End If
Next
End Sub

'PublicDeclarations Module:
Option Explicit

Public myClassModule As New EventClassModule
Public Const dsPlot As String = "DSPlot.ch"
Public dsChart As Chart

Public Sub init()
Set dsChart = Charts(dsPlot)
Debug.Print dsChart.Name
Set myClassModule.myChartClass = dsChart
Debug.Print myClassModule.myChartClass.Name
End Sub

'EventsClassModule:
Option Explicit
Public WithEvents myChartClass As Chart

Private Sub myChartClass_Select(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long)

Dim ser As Series, xPt As Double, yPt As Double
Application.ScreenUpdating = False
Select Case ElementID
Case xlSeries
' Arg1 reports the index of the selected series in the
' series collection
Set ser = ActiveChart.SeriesCollection(Arg1)
' Arg2 reports -1 if the whole series was selected,
' otherwise it reports the index of the point selected
' in the series
If Arg2 = -1 Then
MsgBox "Click again to refine your selection to a
point."
Else
MsgBox "(X,Y) = " & "(" & ser.XValues(Arg2) & "," &
ser.Values(Arg2) & ")"

End If
End Select
End Sub

"Rob Bovey" wrote in message
...
"Mr.RockDoc" wrote in message
...
Rob, This is exactly the process that I am using for
initialization.
When
I put the debug statements in the init routine, they all print out
the
same
name which is "DSPlot.ch" which is correct. However, in looking
at
the
dsPlot object, it shows nothing and therefore the eventclassmodule
doesn't
work when I click on the "DSPlot.ch"

Hi Rock,

In the following chunk of code from the init procedu

Set dsChart = Charts(dsPlot)
Debug.Print dsChart.Name
Set myClassModule.myChartClass = dsChart
Debug.Print myClassModule.myChartClass.Name

dsPlot is a String constant. It will not lose its value under any
circumstances. You say that both of the Debug.Print statements print
the
same name. This means at the point when the initi procedure was run,
the
dsChart object variable and the event handler were both set
correctly.

Then you say your event handler doesn't work when you click on
the
chart. That tells me that at some point between when the init
procedure
was
run and when you clicked on the chart you must have run code
somewhere
that
wiped out the public variables again (maybe in an event procedure?)

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *