Not adding much to the discussion but, by way of an example, here's some
code. First create a user form called frmMessage and add a label to it
called lblMessage

In frmMessage's Initialize routine put Me.Hide so it doesn't display at
startup.

Then you're ready to enter the following code in your worksheet.

Private Sub messageTest()

Load frmMessage

For r = 1 To 20

DoEvents

pause 'Wait a second to slow down execution to human readable
speed.

Cells(r, 1) = r

If r > 4 Then
frmMessage.lblMessage.Caption = "Stuff is happening..." &
vbCrLf & "Please Wait"
frmMessage.Show False
End If

If r > 15 Then frmMessage.lblMessage.Caption = "Nearly done"

Next


unload frmMessage

End Sub

Sub pause()

Dim startTimer As Single

startTime = Timer

While Timer - startTime < 1
DoEvents
Wend
End Sub