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

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

wrote in message
ups.com...
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

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
'-------------



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

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



"Jim Cone"
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

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.



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



"Jim Cone"
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