Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change event not recognized | Excel Programming | |||
Public variable | New Users to Excel | |||
Passing Public Module Vairable to a Worksheet Event | Excel Programming | |||
Public Variable | Excel Programming | |||
public variable | Excel Programming |