I have been trying to get the following to work but when clicking the
Yes to All the userform re-appears each time it performs the routine.
Anyone know how to get it to appear only the once?


Module code:


Public Sub test()
Dim MsgForm As UserForm1
Dim nResult As Long


Set MsgForm = New UserForm1
Load MsgForm
With MsgForm
.Label = "My Prompt"
.Show
nResult = .Result
End With
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


Userform1 code:


Public nResult As Long


Property Let Label(sLabel As String)
Label1.Caption = sLabel
End Property


Property Get Result() As Long
Result = nResult
End Property


Private Sub CommandButton1_Click()
nResult = -1 'Yes
Me.Hide
End Sub


Private Sub CommandButton2_Click()
nResult = 0 'No
Me.Hide
End Sub


Private Sub CommandButton3_Click()
nResult = 1 'Yes to all
Me.Hide
End Sub

I can't reproduce your problem, mainly because I don't understand your
problem. Have you tried stepping through the code line-by-line to determine
what is causing the form to reappear? Of course, every time you run Test the
form will be displayed.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

wrote in message
ups.com...
I have been trying to get the following to work but when clicking the
Yes to All the userform re-appears each time it performs the routine.
Anyone know how to get it to appear only the once?


Module code:


Public Sub test()
Dim MsgForm As UserForm1
Dim nResult As Long


Set MsgForm = New UserForm1
Load MsgForm
With MsgForm
.Label = "My Prompt"
.Show
nResult = .Result
End With
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


Userform1 code:


Public nResult As Long


Property Let Label(sLabel As String)
Label1.Caption = sLabel
End Property


Property Get Result() As Long
Result = nResult
End Property


Private Sub CommandButton1_Click()
nResult = -1 'Yes
Me.Hide
End Sub


Private Sub CommandButton2_Click()
nResult = 0 'No
Me.Hide
End Sub


Private Sub CommandButton3_Click()
nResult = 1 'Yes to all
Me.Hide
End Sub

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