PDA

View Full Version : Solved: Automatic change in Title Bar



gnod
08-23-2007, 09:51 AM
Hi,

i use this code to insert the deployment date in the title bar but when i save it in another filename the title didn't change automatically..


Private Sub Workbook_WindowActivate(ByVal Wn As Window)
' Insert the Deployment Date and Version in the Title Bar
Wn.Caption = ThisWorkbook.Name & " (Deployment Date: Aug. 24, 2007 - ver 1.0)"
End Sub

thanks..

paulked
08-23-2007, 10:08 AM
I just tried this and unless you re-activate the window the title stays the same. Eg Minimise then Maximise.

Regards

Paul Ked

Bob Phillips
08-23-2007, 10:15 AM
Put this in Personal.xls



Public WithEvents App As Application

Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
Wn.Caption = Wb.Name & " (Deployment Date: Aug. 24, 2007 - ver 1.0)"
End Sub

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
App_WindowActivate Wb, Windows(Wb.Name)
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

lenze
08-24-2007, 02:55 PM
Perhaps you will find this useful??
http://vbaexpress.com/kb/getarticle.php?kb_id=877

Although xld's code is probably better
lenze

Bob Phillips
08-24-2007, 03:49 PM
Write it up as KB :-)

gnod
08-29-2007, 09:54 AM
Put this in Personal.xls



Public WithEvents App As Application

Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
Wn.Caption = Wb.Name & " (Deployment Date: Aug. 24, 2007 - ver 1.0)"
End Sub

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
App_WindowActivate Wb, Windows(Wb.Name)
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub



how do i put that code in personal.xls??

Bob Phillips
08-29-2007, 10:06 AM
The details are in my previous post.

gnod
09-03-2007, 08:02 AM
but if i use xld's code the caption will apply to all newly created file and open file.. i only want my workbook to have a caption that's why i put it in Workbook_WindowActivate but itwill not automatically change when i use Save As unless you select other file then go back to my workbook to activate the event procedure..

rory
09-03-2007, 08:21 AM
You could change your code in the ThisWorkbook module to this:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
Application.OnTime Now(), "UpdateCaption"
End If
End Sub
Public Sub Workbook_WindowActivate(ByVal Wn As Window)
' Insert the Deployment Date and Version in the Title Bar
Wn.Caption = ThisWorkbook.Name & " (Deployment Date: Aug. 24, 2007 - ver 1.0)"
End Sub


then add this to a normal module:
Sub UpdateCaption()
Call ThisWorkbook.Workbook_WindowActivate(ThisWorkbook.Windows(1))
End Sub

gnod
09-03-2007, 09:11 AM
Thanks.. :clap: :yes