Update Caption with VBA
The following Excel VBA procedure is very similar to the article on Status Bar Update with VBA. The primary point of difference being where the message appears. The caption which will update automatically is at the top of the screen while the Status Bar is at the bottom of the screen. As with the status bar procedure it is of particular us in Excel for lengthy VBA procedures. The following is an example of a caption update in Excel.
Option Explicit
Sub PageTopCaption() 'Excel VBA to update caption.
Dim i As Integer
Application.StatusBar=False
End Sub
Sub PageTopCaption() 'Excel VBA to update caption.
Dim i As Integer
For i=1 To 10
Application.Caption="You are - " & i & " of 10: " & Format(i / 10, "0%")
Next i
Application.StatusBar=False
End Sub
The above VBA coding is a simple 10 iteration loop which will show what stage the code is upto using a percentage indicator. If you copy the code into a regular module and press F8 10 times you will see the caption at the top of the screen update.