Public variable not recognized in event
 

I have created a macro that runs through a bunch of cells adjusting dat
in other cells. I want this macro to be run whenever there is a chang
(DDE) in some cells, so I thought I would just put a cal
"Application.Run MyMacro
" in the Worksheet_Change event. However when I did that it resulte
in Out of Stack Space errors because the macro itself would change dat
on the worksheet. So I thought I would create a Public Variable in th
Workbook Global Declarations as "Public glRecChanges As Boolean", an
then put this in the Worksheet_Change event...


Code
-------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If !glRecChanges Then
glRecChanges = True

Application.Run "'CWServ NetDDE Test.xls'!RecordChanges"
glRecChanges = False
End If
End Su
-------------------


However it says that the glRecChanges is an "Invalid or Unqualifie
Reference".

What am I doing wrong?

TIA,

Ke

--
kblu
-----------------------------------------------------------------------
kblum's Profile: http://www.excelforum.com/member.php...fo&userid=3720
View this thread: http://www.excelforum.com/showthread.php?threadid=56920

Public variable not recognized in event
 

Better would be

Private Sub Worksheet_Change(ByVal Target As Range)
On Error goto ErrHandler
Application.Events = False
RecordChanges
ErrHandler:
Application.EnableEvents = True
End Sub



--
Regards,
Tom Ogilvy



"kblum" wrote in
message ...

I have created a macro that runs through a bunch of cells adjusting data
in other cells. I want this macro to be run whenever there is a change
(DDE) in some cells, so I thought I would just put a call
"Application.Run MyMacro
" in the Worksheet_Change event. However when I did that it resulted
in Out of Stack Space errors because the macro itself would change data
on the worksheet. So I thought I would create a Public Variable in the
Workbook Global Declarations as "Public glRecChanges As Boolean", and
then put this in the Worksheet_Change event...


Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If !glRecChanges Then
glRecChanges = True

Application.Run "'CWServ NetDDE Test.xls'!RecordChanges"
glRecChanges = False
End If
End Sub
--------------------


However it says that the glRecChanges is an "Invalid or Unqualified
Reference".

What am I doing wrong?

TIA,

Ken


--
kblum
------------------------------------------------------------------------
kblum's Profile:
http://www.excelforum.com/member.php...o&userid=37204
View this thread: http://www.excelforum.com/showthread...hreadid=569203