I have added a Class to my personal.xls which shows the full path of
the workbook in the caption. It all works except when I open a new
workbook. Is there something I am missing? Code below. TIA

Option Explicit
Public WithEvents App As Application
Private Sub App_WindowActivate(ByVal wb As Workbook, ByVal Wn As
Window)
Wn.Caption = wb.FullName
End Sub


Greg

What do you mean by it doesn't work? The event will fire when you create
a new workbook.

If you mean you don't get the full path this is correct behaviour - the
workbook is not saved and therefore doesn't have a full path.

HTH,
Gareth

GregR wrote:
I have added a Class to my personal.xls which shows the full path of
the workbook in the caption. It all works except when I open a new
workbook. Is there something I am missing? Code below. TIA

Option Explicit
Public WithEvents App As Application
Private Sub App_WindowActivate(ByVal wb As Workbook, ByVal Wn As
Window)
Wn.Caption = wb.FullName
End Sub


Greg

Gareth, you're right I don't get the full path, but I have save the WB,
even closed it and reopened it, still no full path.

Greg

Hi Greg,

it works for me i.e. I don't need to close the workbook. Just toggle
between two workbooks and it updates.

I'd try checking your event is still firing - maybe you were tinkering
and crashed out of your code, that's normally enough to stop your app
level events firing.

If not that, maybe something is interfering with it? I'm using XL2003
SP1 btw.

Gareth

GregR wrote:
Gareth, you're right I don't get the full path, but I have save the WB,
even closed it and reopened it, still no full path.

Greg

Greg,

GregR wrote:
Gareth, you're right I don't get the full path, but I have save the
WB, even closed it and reopened it, still no full path.

This should definitely work. When you open a new workbook, you won't see
the full path, as the file has not been saved. Even after you save the
workbook, you won't see the path because the WindowActivate event doesn't
fire. But when you reopen the saved workbook, you should see the full path
in the window's titlebar.

How are you instantiating your object and setting the application reference?
Something like this in a standard module?

Dim c As Class1

Sub start()
Set c = New Class1
Set c.App = Application
End Sub

Sub stopit()
Set c.App = Nothing
Set c = Nothing
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Jake and Gareth, using Excel 2000 and instantiating the class with this
code in "ThisWorkbook"

Option Explicit
Dim AppClass As New ClsAppEvents
Private Sub Workbook_Open()

Set AppClass.App = Application
End Sub

It fires OK, if I close and reopen Excel

Greg

Strange - sounds like it should work to me.

I remain convinced (probably wrongly) that this is an event firing issue
i.e. your AppClass becomes uninstantiated for some reason - maybe an
unhandled error in another App. Try this:

Place a line:

Msgbox "Bing!"

in your WithEvents sub. It will obviously fire every time you change to
a different workbook.

When you create a new workbook - does it still fire?

What about going to an existing workbook?

What service pack / release are you running?

G

GregR wrote:
Jake and Gareth, using Excel 2000 and instantiating the class with this
code in "ThisWorkbook"

Option Explicit
Dim AppClass As New ClsAppEvents
Private Sub Workbook_Open()

Set AppClass.App = Application
End Sub

It fires OK, if I close and reopen Excel

Greg

Gareth, I added the class to my Book.xlt as well and all works just
fine now. Thank you very much for your input and patience.

Greg

You're welcome. Glad you got it worked out.

GregR wrote:
Gareth, I added the class to my Book.xlt as well and all works just
fine now. Thank you very much for your input and patience.

Greg