Adding an Alert or Confirm Box to an Excel Macro

[Pudsters]

I created a Button macro for clearing a form (an Invoice) below:

Sub clear_invoice()
'
' clear_invoice Macro
' Macro recorded 03/27/2006
'

'
Range("C1521").Select
Selection.ClearContents
Range("H27").Select
End Sub

-----------------------------

In case someone hits the Clear button by mistake,

How can I make a box popup first, saying "You are about to clear the Invoice. Click Yes to proceed or No to cancel"

Or something to that effect???

 

[AnotherHiggins]

For future reference, VBA questions such as this should be posted in forum707, the VBA Visual Basic for Applications (Microsoft) Forum.

Try this:

msgBody = "You are about to clear the Invoice." & vbNewLine & _
    "Click Yes to proceed or No to cancel."
msgType = vbOKCancel + vbCritical
msgTitle = "Clear Invoice Verification"

sndmail = MsgBox(msgBody, msgType, msgTitle)
If sndmail = vbCancel Then End

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Pudsters]

John, I'm an Excel user, just made my first Macro with the recorder the other day, so VBA is all new to me.

I appreciate your help and time but I really don't know what to do with that code you provided. Do I insert into my Macro, add it to it, or replace it or what?

Thank you

 

[AnotherHiggins]

Just copy the code above and paste it to the beginning of your code.

Using the macro recorder is a great way to start learning about VBA. Just observe the code that is produced and step through it one line at a time with F8. Pretty soon you'll start to see how things work.

Here is what the whole thing will look like. I've shortened your original code a bit.

Sub clear_invoice()
' clear_invoice Macro
' Macro recorded 03/27/2006

msgBody = "You are about to clear the Invoice." & vbNewLine & _
    "Click Yes to proceed or No to cancel."
msgType = vbOKCancel + vbCritical
msgTitle = "Clear Invoice Verification"

sndmail = MsgBox(msgBody, msgType, msgTitle)
If sndmail = vbCancel Then End

Range("C15:D21").ClearContents
Range("H27").Select
End Sub

If you want to play with the message box to see the various options, replace the variables with their 'values', like this:
sndmail = MsgBox("You are about to clear the Invoice." & vbNewLine & _
"Click Yes to proceed or No to cancel.", vbOKCancel + vbCritical, _
"Clear Invoice Verification")

If you place your cursor at the beginning of "VbOKCancel" and right click > List Constants, several different options will pop up. You can play around with different options to see what the msgbox looks like.

Once you're comfortable with msgboxes and editing recorded code, you'll be able to do amazing things!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.