Try this:

Public Sub test()

Dim MsgForm As UserForm1
Static nResult As Long


Set MsgForm = New UserForm1

If nResult < 1 Then
Load MsgForm

With MsgForm
.Label = "My Prompt"
.Show
nResult = .Result
End With
End If

Unload MsgForm

Set MsgForm = Nothing

Select Case nResult
Case -1
'Do "Yes" stuff
Case 0
'Do "No" stuff
Case 1
'Do "Yes to all" stuff
End Select

End Sub

At some point (in the procedure you're calling test from), you're going to
want to reset nResult, otherwise the program will never prompt the user
again- until the workbook is closed. So make sure you do this.

--
Chris Farkas
Excel/Access Developer
www.eAlchemy.biz