Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Status Bar more complex
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Status Bar more complex
Steven,
There are a few things needed he 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 --------------------------------------------------------------------------------------------- "Steven" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex color fill conditions- if statements or complex formula? | Excel Discussion (Misc queries) | |||
reuest formula for auto update status & status date | Excel Worksheet Functions | |||
Status Bar | Excel Programming | |||
Complex Index Match Help (or at least complex to me) | Excel Discussion (Misc queries) | |||
Need a msg box that displays time status or loading status | Excel Programming |