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