Changing Workbook Caption in Excel07

[AgC]

Hello,

I am trying to replace the caption of an Excel workbook with a custom caption. I've used application.caption = "custom caption" but this only adds my custom caption it doesn't remove the file name. I found the following code:


Option Explicit
Private Declare Function FindWindow _
Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function SetWindowText _
Lib "user32" Alias "SetWindowTextA" _
(ByVal hwnd As Long, _
ByVal lpString As String) As Long

Sub AlterExcelCaption(strCaption As String)
SetWindowText FindWindow("XLMAIN", Application.Caption), strCaption
End Sub

Sub test()
AlterExcelCaption "Just testing"
End Sub


This code works in Excel03 but not in 07. I think the problem is with "XLMAIN". Is there someone who can help me?

Thank you in advance.

 

[tweedle]

Does this help you out?

ActiveWorkbook.Application.Windows(1).Caption = "The best workbook ever!"

 

[AgC]

tweedle,

Thanks for the effort but it doesn't help. I want the caption to be totally customized (using the "Caption" function only adds to whatever is there).

I believe I need to use a dll as I've shown in my sample code but I can't get it to work in 2007 (it works in 2003).

 

[AgC]

Just checking in to see if I got any responses.

 

[Jaafar Tribak]

Maybe :

Sub test()

    Dim wnd As Window
    
    For Each wnd In Application.Windows
        wnd.Caption = ""
    Next
    Application.Caption = "Just testing"

End Sub

 

[AgC]

Jaafar,

You nailed it! Thanks so much.

 

[AgC]

Jaafar (or anyone who can help),

Your answer worked, however, I just notice that when the application is minimized the Caption is blank. Is there a way around this?