View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.programming
ben ben is offline
external usenet poster
 
Posts: 232
Default Application events

hmmmm i don't know about that, i only have one worksheet in the workbook that
was being caught. is there a change it would do so if they were group to
another workbook? (as far as i know i ahve no sheets grouped)
Ben

--
When you lose your mind, you free your life.


"Dave Peterson" wrote:

No help here, but I put this in the ThisWorkbook module:

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
End Sub
Private Sub xlApp_SheetChange(ByVal wks As Object, ByVal target As Range)
Dim resaleactive As Long
resaleactive = 33
If resaleactive = 0 Then Exit Sub
worksheetchange wks, target
End Sub


And this in a general module:
Option Explicit
Sub worksheetchange(wks As Worksheet, target As Range)
MsgBox target.AddressLocal
End Sub

And the only way I could see the problem you had was to have sheets grouped
together and then making a change to one of the grouped worksheets. (It cycled
through each of the grouped sheets.)

I used xl2003 in my test.

Is there any chance that the sheets were grouped???




ben wrote:

I have an App_sheetchange sub that catches changes to all worksheets and
decides which sub to call based on which sheet, it catches the changes and
evaluates all right, only problem is it repeats itself multiple times (43 to
be exact) on one little sheet change.

Private Sub App_SheetChange(ByVal wks As Object, ByVal target As Range)
If resaleactive = 0 Then Exit Sub
worksheetchange wks, target
End Sub

Sub worksheetchange(wks As Worksheet, target As Range)
MsgBox target.AddressLocal
End Sub

for some reason when stepping through after the end sub command for
worksheetchange it will loop right back the command that called it and repeat
that for quite a while then just end inexplicably
any thoughts on why?
--
When you lose your mind, you free your life.


--

Dave Peterson