How to Create “Are you sure you want to ….” MsgBox
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!!!
Software/Hardware used:
Software/Hardware used:
ASKED:
April 7, 2009 7:10 PM
UPDATED:
April 16, 2009 9:55 AM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
er ok, still very new to this- I only used VBA for the first time yesterday- what exactly do I wirte? Thanks?
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.
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
thanks!