Can't get Sheet_Change event to fire - please help

[willjohnson33]

Ok, I searched the boards and found thomas cool's solution to making an
event fire from any sheet or worksheet without using class modules. I
have the following code in ThisWorkbook of my add-in

Private Sub xlsMonitor_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
On Error Resume Next
Application.EnableEvents = False
If ActiveCell.Comment.text <> "" Then
Application.StatusBar = ActiveCell.Comment.text
Else
Application.StatusBar = False
End If
Application.EnableEvents = True
End Sub

All I am trying to do is set it up so that any time I am in a cell with
a comment, the comment is displayed in the status bar. I was able to
get similar code to work when I put the code in ThisWorkbook of an
actual workbook using Private Sub Workbook_SheetChange(ByVal Sh As
Object, ByVal Target As Range)


Why will this not work? Any help is greatly appreciated.

Will

 

[NickHK]

Are you trying to create a short cut way of using application Level events ?
Your code will only respond when changes are made in the workbook
"xlsMonitor", not any sheet.
Also is this really an add-in ?
As such, it is not visible and having code in the _Change event makes no
sense.

Follow Chip's method:
http://www.cpearson.com/excel/AppEvent.htm

NickHK

 

[Jim Cone]

This modified code placed in the ThisWorkbook module
works for me. The Workbook_Open sub must run first...
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


Option Explicit
Public WithEvents xlsMonitor As Excel.Application

Private Sub xlsMonitor_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo EnoughSaid
Dim rngCom As Object
xlsMonitor.EnableEvents = False
On Error Resume Next
Set rngCom = Target.Comment
On Error GoTo EnoughSaid
If Not rngCom Is Nothing Then
xlsMonitor.StatusBar = rngCom.Text
Else
xlsMonitor.StatusBar = False
End If
EnoughSaid:
Set rngCom = Nothing
xlsMonitor.EnableEvents = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlsMonitor = Nothing
End Sub

Private Sub Workbook_Open()
Set xlsMonitor = Excel.Application
xlsMonitor.EnableEvents = True
End Sub
'-------------


<[email protected]>
wrote in message
Ok, I searched the boards and found thomas cool's solution to making an
event fire from any sheet or worksheet without using class modules. I
have the following code in ThisWorkbook of my add-in

Private Sub xlsMonitor_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
On Error Resume Next
Application.EnableEvents = False
If ActiveCell.Comment.text <> "" Then
Application.StatusBar = ActiveCell.Comment.text
Else
Application.StatusBar = False
End If
Application.EnableEvents = True
End Sub

All I am trying to do is set it up so that any time I am in a cell with
a comment, the comment is displayed in the status bar. I was able to
get similar code to work when I put the code in ThisWorkbook of an
actual workbook using Private Sub Workbook_SheetChange(ByVal Sh As
Object, ByVal Target As Range)
Why will this not work? Any help is greatly appreciated.
Will

 

[Jim Cone]

And as NickHK mentioned this will not work in an add-in.
Jim Cone
San Francisco, USA



"Jim Cone" <[email protected]>
wrote in message
This modified code placed in the ThisWorkbook module
works for me. The Workbook_Open sub must run first...
-snip-
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

 

[willjohnson33]

Thanks for the replies. I created an event class and then modified
Jim's code slightly and it works. Yes, this is in an add-in.