The November 1997 edition of Visual Basic Programmer's Journal had an article
about creating a self-clearing messagebox. I made a clumsy attempt to implement
it in Excel VBA. I created a tiny userform (frmNuMsg1) that contained only a
single command button (cmdMsg), which had its AutoSize property set to True. The
Click event for cmdMsg would unload the form, but an OnTime function call could
also unload it. In a code module I put the following two subroutines:

Sub ShowNuMsg(InMsg As String, Optional InTitle = "Message", Optional HowLong
= 0)
Dim Secondz As String
'If InMsg param is NULL (""), exit sub.
If InMsg$ = "" Then Exit Sub
'If duration param is 0 (or omitted), compute a default duration based on
message
'length. Allows about 1.5 seconds per line
If HowLong = 0 Then
HowLong = CInt(Len(InMsg$) / 45) + 2
End If
Secondz$ = "00:00:" & Trim(Str$(HowLong))
'Start the timer (OnTime function) to remove the message form automatically, if
'the user doesn't remove it first.
Application.OnTime Now + TimeValue(Secondz$), "KillNuMsg"
'Reposition & resize the userform to just fit the command button.
frmNuMsg1.Caption = InTitle
frmNuMsg1.cmdMsg.Caption = InMsg$
frmNuMsg1.cmdMsg.Top = frmNuMsg1.Top
frmNuMsg1.cmdMsg.Left = frmNuMsg1.Left
frmNuMsg1.Height = frmNuMsg1.cmdMsg.Height + 18
'84 seems to be the minimum width for a userform
If frmNuMsg1.cmdMsg.Width < 84 Then
frmNuMsg1.cmdMsg.Width = 80
End If
frmNuMsg1.Width = frmNuMsg1.cmdMsg.Width + 4
'Display the message form.
frmNuMsg1.Show
End Sub

Private Sub KillNuMsg()
If frmNuMsg1.Visible = True Then
Unload frmNuMsg1
End If
End Sub

You can tell it how long to remain displayed, or let it come up with a duration
based on the message length.
Call ShowNuMsg("This is a test", "Test #1", 9) 'Displays for 9 seconds
Call ShowNuMsg("This is a test", "Test #1") 'Displays for about 2 seconds