How to Create “Are you sure you want to ….” MsgBox

105 pts.
Tags:
Excel 2003
Excel macros
Microsoft Excel
VBA
I am creating a userform at the bottom of which there are various commands including "Save". When you click on this I ahve written in a series of checks to make sure that all the fields are filled in correctly but after that I want an "Are you sure you want to save" box with the options Yes and Cancel. (Cancel could be No if it makes it easier). So far I have written: ' Message to check first Dim confirm As VbMsgBoxResult confirm = MsgBox("Are you sure you want to submit?", _ vbYesNo, "WARNING: Submit Data") If confirm = 6 Then ' Write data to worksheet RowCount = Worksheets("Database").Range("A2").CurrentRegion.Rows.Count With Worksheets("Database").Range("A2") .Offset(RowCount, 0).Value = Me.txtPN.Value .Offset(RowCount, 1).Value = Me.txtCd.Value .Offset(RowCount, 2).Value = Me.txtDoj.Value etc... (there's a lot!) End With ' replace text content Else ???? ??? ??? ??? End If Then there's a bit more at the bottom so after you've saved the form clears. the bit I'm stuck with is the Else... i.e what do i write to make that message box disappear and return to the form as it is? I have a cancel command already on the form but it's code is just: Private Sub cmdCancel_Click() Unload Me End Sub I have the deadline fo tomorrow for my boss! Help!!!
ASKED: April 7, 2009  7:10 PM
UPDATED: April 16, 2009  9:55 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Once you click on a message box response, the box will go away automatically. You If statement will work when they click on Yes, as it should. The else can be ELSE confirm=7 Then.

————

You don’t need to put something in the ELSE part. If the user click on ‘NO’ or ‘CANCEL’, just do nothing (i.e. don’t use an ELSE).

Just make sure to put all your code to save and clear the form inside the IF part, then close the block (END IF) without an ELSE.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Dontgetit
    er ok, still very new to this- I only used VBA for the first time yesterday- what exactly do I wirte? Thanks?
    105 pointsBadges:
    report
  • Dontgetit
    oops. not quite sure why I put a ? mark after the thanks... no question about that.... ok so i tried im confirm As VbMsgBoxResult confirm = MsgBox("Are you sure you want to submit?", _ vbYesNo, "WARNING: Submit Data") If confirm = 6 Then Else confirm=7 Then but I'm guessing that wasn't very clever because it doesn't work at all.
    105 pointsBadges:
    report
  • Ledlincoln
    Does this help? I included a popup message with the value of the confirm variable in its title bar. Sub confirmDialog() ' Message to check first Dim confirm As VbMsgBoxResult confirm = MsgBox("Are you sure you want to submit?", _ vbYesNo, "WARNING: Submit Data") If confirm = 6 Then ' Write data to worksheet MsgBox "confirmed", , confirm Else ' no code required here; in fact, you can leave out the Else clause MsgBox "not confirmed", , confirm End If End Sub or, leaving out the Else section: Sub confirmDialog() ' Message to check first Dim confirm As VbMsgBoxResult confirm = MsgBox("Are you sure you want to submit?", _ vbYesNo, "WARNING: Submit Data") If confirm = 6 Then ' Write data to worksheet MsgBox "confirmed", , confirm End If End Sub
    1,620 pointsBadges:
    report
  • Dontgetit
    thanks!
    105 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following