I have an excel VBA macro which does continuous updates of the database.
I trigger the macro using ActiveX Controls, "Start" button on the excel sheet.
I want to display the status as "Working" in one of the cells of the spreadsheet while it is working properly
In case of some error it should display "Stopped Working".
How can I do that?
Put this in your button's OnClick method:
Public Sub CommandButton1_Click()
On Error GoTo ErrorHandler
Dim msgRange As Range
' change this to the cell you want to update
Set msgRange = ThisWorkbook.Sheets("Sheet1").Range("A1")
msgRange.Value = "Working"
' your code goes here
msgRange.Value = "Completed"
Set msgRange = Nothing
Exit Sub
ErrorHandler:
msgRange.Value = "Stopped working: " & Err.Description
Set msgRange = Nothing
End Sub
Application.StatusBar instead of some random cell! Otherwise this is a fine approach.
Application.StatusBar as my users never expect to see anything meaningful at the bottom of the screen... I find it more obvious to put a status message right next to the button they've just clicked. But to each their own!or you can use Application.StatusBar which is the usual way of displaying updates:
Application.StatusBar = "Updating..."
