Putting a form into a macro string

[scott]

I have created a macro in excel that clears all input data and assigned it to
a button. I need to put in a pop-up form that asks "Are you Sure" (yes/no).
Yes continues the macro; No puts you back into the spreadsheet.

 

[Orion Cochrane]

Try this:
Confirm = MsgBox("Are you sure?", vbQuestion + vbYesNo, "Confirm Delete")
If Confirm = Yes Then
Application.Run (Macro)
Else: Exit Sub
End If

You can use either Exit Sub or End. The vbQuestion for the buttons syntax
adds a question mark icon to the left of the text in the message box ("Are
you sure?"). The beauty of using vbYesNo is that the user cannot close the
box without answering it. Just copy and paste the above code after your
procedure name. Hope that helps!

 

[Gord Dibben]

Sub test()
With Selection
If MsgBox(prompt:= _
"Are you sure you want to do this", _
Buttons:=vbYesNo) = vbNo Then End
..ClearContents
End With
End Sub


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Or you could just ask the question at the top of your existing routine:

Option Explicit
Sub YourSubNameHere()

'your declaration statements
dim Resp as long 'added

resp = msgbox(Prompt:="Are you sure?", buttons:=vbyesno)

if resp = vbno then
exit sub
end if

'your real code here
End Sub

 

[scott]

Thanks for the info. It's almost there. The MsgBox pops up. No works
correctly, however when I select Yes nothing happens. How do I get it to
step back into the macro.

 

[Orion Cochrane]

That's what I said. Did you put the code at the beginning of your macro?

 

[scott]

Yes.

Sub Clear_Data()
'
' Clear_Data Macro
' Macro recorded 8/25/2008 by Scott D Webster
'

'
Confirm = MsgBox("Are you sure?", vbQuestion + vbYesNo, "Confirm Delete")
If Confirm = Yes Then
Application.Run (Macro)
Else: Exit Sub
End If


Sheets("1").Select
Range("A4:H42").Select

 

[Dave Peterson]

I was answering the original post--not responding to your message.

Your message wasn't there when I retrieved the headers, so I answered the
question.

 

[Dave Peterson]

And my code didn't call another subroutine. So there was a difference.

 

[Dave Peterson]

Option Explicit
Sub YourSubNameHere()

'your declaration statements
dim Resp as long 'added

resp = msgbox(Prompt:="Are you sure?", buttons:=vbyesno)

if resp = vbno then
exit sub
end if

worksheets("1").range("A4:H42").clearcontents

End Sub

 

[Dave Peterson]

Actually, I did see your reply. I just didn't see the reason to call the other
routine.