Excel's Status Bar

[Philcjr]

Hello all from NJ,

Question: Is it possible to have a message box appear and display the text/message that appears in Excel's Status Bar? And have itself close out when the message is done?

Phil

[Zack Barresse]

Hiya Phil!

Not sure what you're after, but if I'm understanding you ...

Option Explicit

Sub foo()
    MsgBox Application.StatusBar
    Application.StatusBar = False
End Sub

Are you putting custom text in your status bar?

[Philcjr]

Zack,

I think this will work... How simple once you see the code.

What I am trying to do is this, when the workbook closes it saves a backup of itself in another folder with today's date. I see that the status bar displays a message when it is doing the work (saving the file). I thought it would be nice to show a message box with the status showing the user whats going on.

I will insert these lines of code and let you all know how it turns out.

Phil

[Philcjr]

Zack,

Need some help here... this is not working. Here is my code. All I get is a message box that displays "FALSE" and an OK button

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.StatusBar = False
MsgBox Application.StatusBar
With ThisWorkbook
        .SaveCopyAs ("G:\team\PS&L\GA Spreadsheet\Back-up files\ " & Format(Date, "mm-dd-yy") & " " & .Name)
    End With
End Sub

[Zack Barresse]

Hmm, I don't think it will work for that type of application. While the workbook is saving (one snigle event) you won't be able to access anything. It's like executing one single line of compiled code, we can't do anything until that line of code has been run through.

You can, however, manually manipulate the StatusBar to show the user what's going on. If you do this, don't forget to set it back to False (as I did with the last line of code above). There are also other methods of Progress Bars, if you'd rather.

[Philcjr]

The status bar shows "Now Saving Temporary File".

This is a "Nice-To-Have" to add to the file. Any thoughts in how I could simply have a message pop-up vs. the bar on the bottom of their screen to say/show "Now Saving Temporary File"?

This action would not trigger when the file is "Saved" but rather when it "Closes"

Am I reading correctly, that when the workbook is closing it is difficult or not possible to do what I am trying to do?

Thanks again,
Phil

[Bob Phillips]

The status bar shows "Now Saving Temporary File".

This is a "Nice-To-Have" to add to the file. Any thoughts in how I could simply have a message pop-up vs. the bar on the bottom of their screen to say/show "Now Saving Temporary File"?

This action would not trigger when the file is "Saved" but rather when it "Closes"

Am I reading correctly, that when the workbook is closing it is difficult or not possible to do what I am trying to do?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Const TM_TEXT As String = "Now Saving temporary file"
Const TM_TITLE As String = "Saving File"
Const TM_DURATION As Long = 5 'seconds
Dim WSH As Object
Set WSH = CreateObject("WScript.Shell")
    WSH.Popup TM_TEXT, TM_DURATION, TM_TITLE, vbInformation
    With ThisWorkbook
 .SaveCopyAs ("G:\team\PS&L\GA Spreadsheet\Back-up files\ " & Format(Date, "mm-dd-yy") & " " & .Name)
    End With
End Sub

[Philcjr]

This will work just fine.

Thanks all!