UserForm data is resetting after hitting Cancel

jmpatrick

Active Member
Joined
Aug 17, 2016
Messages
477
Office Version
  1. 365
Platform
  1. Windows
This is probably very simple but after working on it for a couple hours I'm stumped.

My UserForm has a Delete button that brings up a confirmation before you delete the record. If you say "yes" the record is deleted as intended. If you say no the form closes without deleting as intended.

I need to add a confirmation that follows a "No" click that says that nothing was deleted...and I don't want the currently displayed data to reset.

VBA Code:
Private Sub cmdDeleteSub_Click()

    Dim CurrentRow  As Long
    Dim Answer      As VbMsgBoxResult
    
    CurrentRow = i
    Answer = MsgBox("Are you sure you want to delete this Subdivision completely?  There is no undo", vbYesNo + vbQuestion, "Delete Record?")
    If Answer = vbYes Then wsDataCenter.Cells(CurrentRow, 1).EntireRow.Delete

With Me
        .SubName.Clear
        .SubCode.Value = ""
        .SubInitials.Value = ""
        .FieldManager.Clear
End With
MsgBox ("Subdivision Successfully Deleted!")
Call UserForm_Initialize
End Sub
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
How about
VBA Code:
Private Sub cmdDeleteSub_Click()

    Dim CurrentRow  As Long
    Dim Answer      As VbMsgBoxResult
    
    CurrentRow = i
    Answer = MsgBox("Are you sure you want to delete this Subdivision completely?  There is no undo", vbYesNo + vbQuestion, "Delete Record?")
    If Answer = vbYes Then
        wsDataCenter.Cells(CurrentRow, 1).EntireRow.Delete
        With Me
            .SubName.Clear
            .SubCode.Value = ""
            .SubInitials.Value = ""
            .FieldManager.Clear
        End With
        MsgBox ("Subdivision Successfully Deleted!")
    
    ElseIf Answer = vbNo Then
        MsgBox "Nothing has been deleted yet"
    End If
    Call UserForm_Initialize
End Sub
 
Upvote 0
Solution
How about
VBA Code:
Private Sub cmdDeleteSub_Click()

    Dim CurrentRow  As Long
    Dim Answer      As VbMsgBoxResult
   
    CurrentRow = i
    Answer = MsgBox("Are you sure you want to delete this Subdivision completely?  There is no undo", vbYesNo + vbQuestion, "Delete Record?")
    If Answer = vbYes Then
        wsDataCenter.Cells(CurrentRow, 1).EntireRow.Delete
        With Me
            .SubName.Clear
            .SubCode.Value = ""
            .SubInitials.Value = ""
            .FieldManager.Clear
        End With
        MsgBox ("Subdivision Successfully Deleted!")
   
    ElseIf Answer = vbNo Then
        MsgBox "Nothing has been deleted yet"
    End If
    Call UserForm_Initialize
End Sub

Perfect. Thanks.
 
Upvote 0
You're welcome and thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top