How to have a VBA cancel the script?

Question

How can I have the VBA code cancel if the cancel button is pressed. I have...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim ws As Worksheet
Set ws = Sheets("EDITS")
Dim tbl As ListObject
Set tbl = ws.ListObjects("Table1")
Dim newrow As ListRow
Set newrow = tbl.ListRows.Add

SavePrompt.Show

With newrow
    .Range(1) = Now
    .Range(2) = SavePrompt.TextBox1.Text
End With

End Sub

and added...

Private Sub CommandButton1_Click()

SavePrompt.Hide

End Sub

And,

Private Sub CommandButton2_Click()

Cancel = True

End Sub

to the Workbook Project Macro section.

But for some reason I'm not sure how to get the code to Cancel since the user realizes they don't want to save the changes they for some reason made to the Workbook. This SavePrompt help diagnose what was changed and when.

Answer 1

I thin your meaning is when user press cancel button , you want to clean list of row.If so just make like the following simple code.

Private Sub ListObject_Delete()
    Dim List1 As Microsoft.Office.Tools.Excel.ListObject = _
        Me.Controls.AddListObject(Me.Range("A1", "D4"), "List1")

    If DialogResult.Yes = MessageBox.Show("Delete the ListObject?", _
        "Test", MessageBoxButtons.YesNo) Then
        List1.Delete()
    End If

End Sub