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]

If you want the message box everytime a row is removed, you can use this:

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
    MsgBox "Most recent submission has been removed.", vbInformation
End If


End Sub

The AND constuct doesnt work as you had tried to implement it. You just need to put the message box as a new line

Let me know if it works for you

Thanks

Caleeco

 

[pahy96]

Awesome! What is it exactly that vbInformation accomplishes?

 

[Caleeco]

Awesome! What is it exactly that vbInformation accomplishes?

Nothing amazing. It just adds the "i" symbol (representing information) to the MsgBox that pops-up.

Similarily you can your 'vbexclamation' for warning MsgBox's

 

[Steve_R]

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

I would like to be able to give you a fulsome, intelligent explanation on usedRange but it's not something I ever studied in detail. It does represent the bottom-most row and right-most column. So, a safe way to get lasRow or lastColumn. However, there are circumstances when it will return an area seemingly outside the range of cells that contains a value. In these circumstances, it needs to be reset. One example I know of is after using ActiveSheet.QueryTables.Add method. However,I just tested mrexcel.com and UsedRange seems to reset properly cells are cleared.

Normally ActiveSheet.UsedRange or x=ActiveSheet.usedrange.rows.count causes a reset but one website I querytable does not reset that way (been using xlUp from bottom of sheet to get around it).

Searching for a reference that may help you I ran across the following (untested until I figure out my problem website).

Excel VBA usedRange Property and reset usedRange

Find the code delete_empty_row() about half way down the page.

 

[Steve_R]

That didn't take too long. Sample code grabs tide data from Bondi Beach.
After cearing cells and querytable, selecting UsedRange gives strange results that are not cleared by
methods normally recommended on forums.

Code from Excel VBA usedRange Property and reset usedRange seems to work OK but does some looping.

Selecting all blank cells and clearing those seemed to me to be a better method if it worked so I wrote some code (FastUsedRangeReset) and tested it. The test was successful.

Sub GetSomeWebData()

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://tides.willyweather.com.au/nsw/sydney/bondi-beach.html", _
        Destination:=Range("$A$1"))
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

       
    'delete the data and querytable
    Cells.Select
    Selection.QueryTable.Delete
    Selection.ClearContents
    
    'Used range should be nothing but
    ActiveSheet.UsedRange.Select
    MsgBox "Used Range Selected"
    
    'attempt to reset the used range using two web-located methods
    ActiveSheet.UsedRange
    a = ActiveSheet.UsedRange.Rows.Count
    MsgBox "UsedRage selected after two 'recommended' reset methods"
    
    'Call delete_empty_row 'Uncomment this line and comment out the next line to test alternative
    Call FastUsedRangeReset
    ActiveSheet.UsedRange.Select
    MsgBox "UsedRange selected after individual cells clear using either called macro"
End Sub


Public Sub delete_empty_row()
    
    ' from http://access-excel.tips/excel-vba-usedrange-property/
    
    Application.ScreenUpdating = False

    For Each usedrng In ActiveSheet.UsedRange
        If usedrng.MergeCells = True Then
            If usedrng.Value = "" Then
                usedrng.Value = ""
            End If
        Else
            If usedrng.Value = "" Then
                usedrng.ClearContents
            End If
        End If
    Next
    
    ActiveSheet.UsedRange
    usedRangeLastColNum = ActiveSheet.UsedRange.Columns.Count
    usedrangelastrow = ActiveSheet.UsedRange.Rows.Count
    
    For r = usedrangelastrow To 1 Step -1
        If Application.WorksheetFunction.CountA(Cells(r, usedRangeLastColNum).EntireRow) <> 0 Then
            Exit For
        Else
            Cells(r, usedRangeLastColNum).EntireRow.Delete
        End If
    Next r
    
    For c = usedRangeLastColNum To 1 Step -1
        If Application.WorksheetFunction.CountA(Cells(1, c).EntireColumn) <> 0 Then
            Exit For
        Else
            Cells(1, c).EntireColumn.Delete
        End If
    Next c
    
    ActiveSheet.UsedRange
    Application.ScreenUpdating = True
End Sub


Sub FastUsedRangeReset()
ActiveSheet.UsedRange.Select

'Select all blank cells in the used range
Selection.SpecialCells(xlCellTypeBlanks).Select
'Clear whatever is in the blank cells that prevents used range from resetting
Selection.Cells.Clear

End Sub

 

[pahy96]

Nothing amazing. It just adds the "i" symbol (representing information) to the MsgBox that pops-up.

Similarily you can your 'vbexclamation' for warning MsgBox's

Oh, nice. Thanks again for all the help!

 

[Steve_R]

Sorry we've side-tracked your thread, pahy96

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

I am still testeing things associated with UsedRange. Testing shows it is not totally reliable unless you start your data at row 1 when counting rows and column 1 when counting columns (and subject to UsedRange not extending beyond the visible limits of your data). Most of use are in the habit of not leaving blank rows or columns but it is a point to remember.

MSDN information about used range:

"A used range includes any cell that has ever been used. For example, if cell A1 contains a value, and then you delete the value, then cell A1 is considered used. In this case, the UsedRange property will return a range that includes cell A1."

The above does not appear to totally accurate in 2010:

Enter anything in B2 of a new sheet; delete the value.
Sub test()
ActiveSheet.UsedRange.Select
End Sub

Selects A1.

More from MSDN on UsedRange affecting file size.

To save memory and reduce file size, Excel tries to store information about the area only on a worksheet that was used. This is called the used range. Sometimes various editing and formatting operations extend the used range significantly beyond the range that you would currently consider used. This can cause performance obstructions and file-size obstructions. You can check the visible used range on a worksheet by using CTRL+END. Where this is excessive, you should consider deleting all the rows and columns below and to the right of your real last used cell and then saving the workbook. Create a backup copy first. If you have formulas with ranges that extend into or refer to the deleted area, these ranges will be reduced in size or changed to #N/A.

Also read " What? Me? Deranged?" in Jeff Weir's article on spreadsheet bloat

From that:

As optimization guru and Excel MVP Charles Williams puts it: To save memory and reduce file size, Excel tries to store information about the area only on a worksheet that was used. This is called the used range. Sometimes various editing and formatting operations extend the used range significantly beyond the range that you would currently consider used. This can cause performance obstructions and file-size obstructions.

Used range reset function code cleaned up (now in my PERSONAL).

Sub ResetUsedRange()

    With ActiveSheet.UsedRange
        On Error Resume Next 'for no blanks exist
        Selection.SpecialCells(xlCellTypeBlanks).Cells.Clear
        On Error GoTo 0
    End With

    End Sub

Debra Dalgleish uses a different method to reset usedrange
While my method works in the circumstance in which it was tested (my problem), Debra's code should work in all other than the merged cells circumstance that she mentions.

 

[Caleeco]

@Steve_R

Amazing! Thanks for all the useful info, I'll definitely be adding this to my archives