Excel UserForm Data Entry Update

Someone emailed me this week, about a problem he was having with my sample Part Data Entry UserForm.

PartsInventoryUserForm01

When I took a look at the workbook, everything seemed okay, and the code had been copied and altered correctly.

Excel Named Table

Then I noticed that there was a formatted Excel table on the data collection sheet, which wasn’t in my original file.

That can cause problems if you’re using Excel VBA to add data to the first blank row on the worksheet.

Change the Last Row Code

In the comments for my Find First Blank Row blog post, Rick Rothstein suggested this code revision:

LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, 
SearchDirection:=xlPrevious, LookIn:=xlValues).Row

Rick mentioned that this formula ignores cells with formulas that are displaying the empty string. If your situation is such that you need to identify formula cells that might be displaying the empty string, then change the xlValues argument to xlFormulas.

Revised Excel VBA Code

So, I changed the Part Data Entry code, to use the Find method for finding the last row. I replaced this old line of code:

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

With this line of code:

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
       SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

Parts Data Entry UserForm With Combo Boxes

There is another version of the Parts Data Entry UserForm, and it’s a little fancier, with combo boxes to select parts and locations.

I’ve updated the Parts Data Entry UserForm With Combo Boxes too, with the revised last row code.

PartsInventoryUserForm02

Get the Updated Sample Files

You can download the updated versions of the parts data entry forms on the Contextures website.

Part Data Entry UserForm

Parts Data Entry UserForm With Combo Boxes

_________________

14 thoughts on “Excel UserForm Data Entry Update”

  1. Your tutorial on how to create a UserForm in Excel was great!! This is a great way to add new data, but what if we want to update data already in the spreadsheet/database? Do you have a tutorial for that? Thanks so much!! David

  2. Dear Sir,
    I went to your site it is nice and got idea how to add an record is it possible you to help on the below topics.
    1.By entiring the more than two data’s in a each colum able to select the total data
    2.Need help in the excel vba macros pls send the smaple form to follow.
    Awaiting your reply
    regards.
    Abrar

  3. Dear Sir,
    Instead of saving data in the same file, I created another one for database purpose only. Using your sample as the main form (PartLocDB.xls), how can I add (instead of the same worksheet)the data directly to other file like (PartLocDB2.xls)as main database.
    Below is the parameters that need adjustment
    Set ws = Worksheets(“PartsData”)

  4. No matter what VBA code I use I cannot get the second line of information (entered from a UserForm) copied into the second line of a Table.
    The first line of information from the UserForm copies just fine into the first line of the Table. It’s the second line that is giving me fits.
    The Code is…Thanks Debra for any suggestions – I’m about Googled Out – smile.
    Private Sub cmdbtnSave_Click()
    Dim NewRowOfData As Long
    Dim ws As Worksheet
    Set ws = Worksheets(“WasteWaterUsage”)
    Set rRng = ws.Range(“B6″)
    ‘Test to see if 1st cell in row is empty
    If IsEmpty(rRng.Value) Then
    NewRowOfData = 6
    Else
    ‘NewRowOfData = ws.Cells(Row.Count, 1).End(xlUp).Offset(1, 0).Row
    NewRowOfData = ws.Cells.Find(What:=”*”, SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    End If
    ‘Input data from UserForm to Worksheet
    With ws
    .Cells(NewRowOfData, 2).Value = Me.txtbStartDate.Value
    .Cells(NewRowOfData, 3).Value = Me.txtbEndDate.Value
    .Cells(NewRowOfData, 8).Value = Me.txtbGallons.Value
    .Cells(NewRowOfData, 5).Value = Me.txtbWeekends.Value
    .Cells(NewRowOfData, 6).Value = Me.txtbIdleDays.Value
    ‘.Cells(NewRowOfData, 2).Activate – this was in another workbook where data input works fine – here gives an error
    End With
    ‘Clear Input Values
    Me.txtbStartDate.Value = “”
    Me.txtbEndDate.Value = “”
    Me.txtbGallons.Value = “”
    Me.txtbWeekends.Value = “”
    Me.txtbIdleDays.Value = “”
    End Sub
    Data begins in column B row 6 but actually covers 9 columns,
    the Table itself covers Columns A through I, A is calculated, therefore actual input begins in column B.
    Best wishes, Mort

    1. @Mort, I’m not clear on what you mean by a second row of data from the User Form.
      Your code is finding the first blank row, and putting all the values in that row.
      Should something be going into the row below that?

  5. Thanks Debra so much – and I get soooo frustrated with the way folks write and seems I’m one of’em – smile.
    Have a Table – will have 6 rows of data for 6 months of information, and about 6-7-8 columns of data per row.
    At the end of the 6 months, the Table will be cleared – have that code, and the formulas will be maintained.
    Beginning with the next 6 months, month 1, user will enter information via a UserForm and that info should be copied to the Table by cmdSave button on UserForm.
    I then need to find the next empty row for which month 2’s data will be inserted. While I try to get month 2 inserted, it never works….now row 1 is not working.
    My Table has headers by the way….I’ve copied I don’t know how many codes and tried them and there are errors with every single one of them – NONE WORK.
    Col A is calculated so entry begins with column B – because there is a “leg” of calculations hanging below the table on the right of it – like a fly lying “L”
    I think I need to do the next empty row specific to column B?
    My brain cells hurt to day – thanks…
    Mort, Dallas

  6. I was correct – the “L” shape is causing the issues, I have to find a way to search for next empty row via column B.

  7. Code works but is seeing wrong place
    NewRowOfData = ws.Cells.Find(What:=”*”, SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    But is finding the row under the lower part of the L in the data range – If I could post a graphic you’d see right away need to search a specific column, like B

  8. The important lesson to be learned here…..regarding adding a new “empty” row to a TABLE
    If there is information below that TABLE the normal method to find the next empty row of the TABLE does not work!!
    The xlup or whatever, due to the fact there is info below the TABLE
    The solution….taaaa daaaaaa
    Work on the TABLE itself
    In abbreviated form insure you include this in your code:
    Dim ws as Worksheet
    Dim tbl As ListObject
    Set tbl = ws.ListObjects (“name of your table here”)
    tbl.ListRows.Add
    Set LastRow = tbl.ListRows(tbl.ListRows.Count).Range
    now enter your data from your UserForm
    Also to note: Only information directly under your TABLE will be moved down as new rows are added.
    If you have information not under the TABLE, that information will stay put.
    I had to add extra blank columns to my TABLE to account for information under the TABLE that
    extended beyond the end of the TABLE.
    Hope this is of some help?

  9. When I use the
    iRow = ws.Cells.Find(What:=”*”, SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    it goes to the end of my table some 25 rows past where I would like it to start.
    How do I get it to start, for example at A8 and look down from there to find the next empty row?
    If I have SUM= in some of these will that cause for a full row?

  10. Debra
    Many thanks for the guidance on https://www.contextures.com/xlForm02.html and other pages.

    I love the userform that I have created for data input / update into a large table.

    A question – userform and associated navigation buttons will work sequentially through each row of the data table.
    If I add filters to the data table – is there any way for the userform to only view the filtered results and the navigation buttons would move me though the filtered rows, not all rows?

    Many thanks

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.