UserForm Undo Button

[pahy96]

I am completely new to VBA and have learned quite a bit in the last week by working slowly on a personal project of mine. I have most of the essentials set up, but it is imperative that I provide the user the ability to undo a data submission when using a User Form.

My submission code is as follows:

Private Sub SubmitCommandButton_Click()

Dim emptyRow As Long


'Make Raw Data Sheet Active
Sheet4.Activate


'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1


'Transfer information
Cells(emptyRow, 1).Value = MonthComboBox.Value
Cells(emptyRow, 2).Value = DayTextBox.Value
Cells(emptyRow, 3).Value = YearTextBox.Value
Cells(emptyRow, 4).Value = CategoryComboBox.Value
Cells(emptyRow, 5).Value = SubcategoryComboBox.Value
Cells(emptyRow, 6).Value = NotesTextBox.Value
Cells(emptyRow, 7).Value = PriceTextBox.Value
Cells(emptyRow, 8).Value = ConsumerComboBox.Value
Cells(emptyRow, 9).Value = WithdrawalTextBox.Value
Cells(emptyRow, 10).Value = DepositTextBox.Value


MsgBox "Submission Successful!"


End Sub

How can I allow an undo button to clear the most recent submission? Would it be as simple as writing code to delete the row above the first empty row?

 

[Caleeco]

The code below would find the last row in Column A which contains data, and then delete the entire row.

Sub DeleteLastEntry()


ActiveSheet.Range("A" & Range("A" & Rows.Count).End(xlUp).Row).EntireRow.Delete


End Sub

You can assign this to a UNDO button on your userform

 

[pahy96]

Yes! That is perfect. Thank you so much!

 

[Steve_R]

The way it is now written, your code will overwrite cells if you have a blank in column A

In a spare sheet complete some values down column A
Run Test1
Delete a value from the middle of the range
Run Test1 again

After that, repat the exercise using Test2

Sub Test1()

emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

Cells(emptyRow, 1).Select

MsgBox "This is where the rest of your code starts writing values"

End Sub


Sub test()

ActiveSheet.UsedRange ' reset used range

nextRow = ActiveSheet.UsedRange.Rows.Count + 1

MsgBox "nextRow = " & nextRow

DelRow = ActiveSheet.UsedRange.Rows.Count

Range("A" & DelRow).Select
MsgBox "delRow = " & DelRow


End Sub


Sub test2()

ActiveSheet.UsedRange ' reset used range

nextRow = ActiveSheet.UsedRange.Rows.Count + 1

MsgBox "nextRow = " & nextRow

DelRow = ActiveSheet.UsedRange.Rows.Count

Range("A" & DelRow).Select
MsgBox "delRow = " & DelRow


End Sub

So, the method used to find DelRow finds the last row in your sheet.

Cells(delrow).EntireRow.Delete

Will delete the last row in your sheet.

 

[Caleeco]

No Problem, glad it worked!

 

[Caleeco]

The way it is now written, your code will overwrite cells if you have a blank in column A

In a spare sheet complete some values down column A
Run Test1
Delete a value from the middle of the range
Run Test1 again

After that, repat the exercise using Test2

So, the method used to find DelRow finds the last row in your sheet.

Cells(delrow).EntireRow.Delete

Will delete the last row in your sheet.

Hi Steve,

I've not come across the 'UsedRange' Property before. I assume that just looks up the maximum range for all columns (defined by which cells are not blank)?

This will be useful, for some things i'm doing at work

 

[pahy96]

As a secondary request, is there a condition I can add (presumably an If...Then) that will disallow the command button to work if there is only data in the first row (i.e. my table headers)?

 

[Caleeco]

As a secondary request, is there a condition I can add (presumably an If...Then) that will disallow the command button to work if there is only data in the first row (i.e. my table headers)?

Assuming your headers are in Row 1, i've modified the code I previously wrote:

Sub DeleteLastEntry()


Dim LastRow As Long


LastRow = Range("A" & Rows.Count).End(xlUp).Row


If LastRow = 1 Then
    'Do Nothing becuase this is the header row
Else
    ActiveSheet.Range("A" & LastRow).EntireRow.Delete
End If


End Sub

 

[pahy96]

Here is something I'm considering

Private Sub UndoCommandButton_Click()

    If ActiveSheet.Rows.Count = 1 Then MsgBox "There is no data to delete."
        Else: ActiveSheet.Range("A" & Range("A" & Rows.Count).End(xlUp).Row).EntireRow.Delete
    End If


MsgBox "Previous Submission Successfully Removed"


End Sub

 

[pahy96]

Assuming your headers are in Row 1, i've modified the code I previously wrote:

Sub DeleteLastEntry()

Dim LastRow As Long


LastRow = Range("A" & Rows.Count).End(xlUp).Row


If LastRow = 1 Then
    'Do Nothing becuase this is the header row
Else
    ActiveSheet.Range("A" & LastRow).EntireRow.Delete
End If


End Sub

I'm trying to get my final MsgBox back in there and I'm not having any success. I've tried the following. Each bold line marks an attempt I've made at adding the MsgBox:

Private Sub UndoCommandButton_Click()

'Make Raw Data Sheet Active
Sheet4.Activate


Dim LastRow As Long




LastRow = Range("A" & Rows.Count).End(xlUp).Row


'Create condition that doesn't allow header row to be deleted


If LastRow = 1 Then
    MsgBox "No data available to delete."
Else
    ActiveSheet.Range("A" & LastRow).EntireRow.Delete [B]AND[/B] [B]MsgBox "Most recent submission has been removed."[/B]
End If


[B]MsgBox "Most recent submission has been removed."[/B]


End Sub