Status Bar more complex

[Steven]

I want to be able to control the status bar. Here is an example of what I am
doing as a test.

I Sheet1 if I change to Column "C" Then the status bar changes to:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Column = 3 Then
oldStatusbar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "I changed this to what I want."
Else
Application.StatusBar = False
End If
End Sub

Now if the Status Bar says "I changed this to what I want." and I hit save
it will go back to "Ready" based on the following code. How would I make the
Status Bar go back to "I changed this to what I want." if that is what was
showing otherwise I would want it to go to "Ready". Note: I also want the
user to be able to see the "Saving ..... .xls " as it happens and then
change back to the "Ready" or "I changed this to what I want." as the case
may be. Here is what I have in the Before_Save

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sFile
'<Optional - this would be before save code>
Cancel = True
Application.EnableEvents = False
If SaveAsUI Then
sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If sFile <> False Then
ThisWorkbook.SaveAs sFile
'<Optional - this would be after save code>
End If
Else
ThisWorkbook.Save '******
'<Optional - this would be after save code>
Application.StatusBar = False
End If
Application.EnableEvents = True
End Sub

Thank you for your help,

Steven

 

[Steven]

Steven,

There are a few things needed here:

In the Before_Save you have Application.StatusBar = False in the wrong
place. It should go right before the ... If SaveAsUI Then

You also need a Sub routine that has the same code as the
Worksheet_SelectionChange. I called this Sub ControlStatusBar and put in
Module1.

Then in the Before_Save you need to have .. Call ControlStatusBar .. before
the Application.EnableEvents = True

Also you have to put .. Call ControlStatusBar .. in the Before_Close. You
have to do this to insure that you get the status bar back to its original
setting when you close the file. Otherwise you may end up with "I changed
this to what I want." if you save the file and are in Column C.

Dougla