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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
'-------------


<(E-Mail Removed)>
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" <(E-Mail Removed)>
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" <(E-Mail Removed)>
> 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