Hello
I am trying to create a msg box so that when the user clicks 'yes' it
triggers a macro to run. However at the moment when I click Yes the script
ends and no macro is run - the same happens for No and Cancel but that is
correct for those selections. The code I'm trying is shown below and sits at
the start of the marco in question. I'm new to this so have not sure what to
do. Any ideas?

Sub clean()
Msg = "Have you saved the current file data?"
Response = MsgBox(Msg, vbYesNoCancel)

If Response = vbYes Then
'<=== runactivemacro clean()
Exit Sub
End If

If Response = vbNo Then
MsgBox "Use the Save As function to Save the file before cleaning the sheet"
Exit Sub
End If

If Response = vbCancel Then
MsgBox "Action Cancelled"
Exit Sub
End If


' clean Macro
' Macro recorded etc etc then all the macro detail is here

This works fine for me

Sub Clean()
Dim Msg As String
Dim Response As Long

Msg = "Have you saved the current file data?"
Response = MsgBox(Msg, vbYesNoCancel)

If Response = vbYes Then
Call Clean2
ElseIf Response = vbNo Then
MsgBox "Use the Save As function to Save the file before cleaning
the sheet"
ElseIf Response = vbCancel Then
MsgBox "Action Cancelled"
End If

End Sub

Sub Clean2()
MsgBox "Clean2"
End Sub



--
__________________________________
HTH

Bob

"Binngo" wrote in message
...
Hello
I am trying to create a msg box so that when the user clicks 'yes' it
triggers a macro to run. However at the moment when I click Yes the
script
ends and no macro is run - the same happens for No and Cancel but that is
correct for those selections. The code I'm trying is shown below and sits
at
the start of the marco in question. I'm new to this so have not sure what
to
do. Any ideas?

Sub clean()
Msg = "Have you saved the current file data?"
Response = MsgBox(Msg, vbYesNoCancel)

If Response = vbYes Then
'<=== runactivemacro clean()
Exit Sub
End If

If Response = vbNo Then
MsgBox "Use the Save As function to Save the file before cleaning the
sheet"
Exit Sub
End If

If Response = vbCancel Then
MsgBox "Action Cancelled"
Exit Sub
End If


' clean Macro
' Macro recorded etc etc then all the macro detail is here

Hi Binngo,

A other way to do could be:

Sub Clean()
Msg = "Have you saved the current file data?"
Select Case MsgBox(Msg, vbYesNoCancel)
Case vbYes
Call Clean2
Case vbNo
MsgBox "Use the Save As function to " & _
"Save the file before cleaning the sheet"
Case Else
MsgBox "Action Cancelled"
End Select

End Sub

Sub Clean2()
MsgBox "Clean2"
End Sub

HTH,

Wouter

Thanks both I will give those a try.

bw Binngo


"Binngo" wrote:

Hello
I am trying to create a msg box so that when the user clicks 'yes' it
triggers a macro to run. However at the moment when I click Yes the script
ends and no macro is run - the same happens for No and Cancel but that is
correct for those selections. The code I'm trying is shown below and sits at
the start of the marco in question. I'm new to this so have not sure what to
do. Any ideas?

Sub clean()
Msg = "Have you saved the current file data?"
Response = MsgBox(Msg, vbYesNoCancel)

If Response = vbYes Then
'<=== runactivemacro clean()
Exit Sub
End If

If Response = vbNo Then
MsgBox "Use the Save As function to Save the file before cleaning the sheet"
Exit Sub
End If

If Response = vbCancel Then
MsgBox "Action Cancelled"
Exit Sub
End If


' clean Macro
' Macro recorded etc etc then all the macro detail is here