Full Name in Workbook Title Bar

  • Thread starter Thread starter Jack Gillis
  • Start date Start date
J

Jack Gillis

I put the following in the Workbook_Open sub of a workbook.

ActiveWindow.Caption = ActiveWorkbook.FullName

It works just fine for that workbook but I really want it to work for
any workbook I open so I tried putting it in the Workbook_open sub of
Personal.xls. I got an error message saying something like "block
variable not set."

What can I do to make the full path name appear for any open workbook
with an entry in Personal.xls?

Thank you.
 
When you have it in your workbook_open event of a specific workbook, then that
event will apply to that workbook.

You'll want to set up an application event that looks for a change to anything
(workbook/window) that can change.

You could do something like this:

In your personal.xls file, hit alt-f11 to go to the VBE.

Click on Insert
and choose Class Module
Make sure it's called Class1 (we'll refer to it later by that name).

Paste this in:

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub xlApp_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
Wn.Caption = Wb.FullName
End Sub


---------
Now rightclick on the ThisWorkbook object and select view code. (You might have
to hit ctrl-R to see the project explorer.)

Paste this in:

Option Explicit
Dim myTitle As Class1
Private Sub Workbook_Open()
Set myTitle = New Class1
Set myTitle.xlApp = Application
End Sub
Private Sub workbook_close()
Set myTitle.xlApp = Nothing
End Sub

==============
You can read a lot more about application events at Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.htm

And workbook/worksheet events at:
http://www.cpearson.com/excel/events.htm

And David McRitchie has some notes at:
http://www.mvps.org/dmcritchie/excel/event.htm

And just getting started???
David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
You can do it all in ThisWorkbook

Public WithEvents xlApp As Excel.Application

Private Sub Workbook_Open()
Set xlApp = Application
End Sub

Private Sub workbook_close()
Set myTitle.xlApp = Nothing
End Sub

Private Sub xlApp_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
Wn.Caption = Wb.FullName
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Thank you very, very much, Dave. I will be breaking new ground for me
but am looking forward to it.
It is not going to be as simple as I thought.

Thanks again.
 
I've seen those threads between lots of people and KeepItCool.

I think the best reference I've seen that describe application events is Chip's
site. And I want to be consistent with Chip's instructions. (Personal choice,
only.)
 
Back
Top