Excel UserForm Data Entry Update

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


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

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.

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 have updated the Parts Data Entry UserForm With Combo Boxes too, with the revised last row code.


Get the Updated Sample Files

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

Excel VBA School

If you're struggling with Excel programming, Chandoo has re-opened his Excel VBA school and registration is ongoing. You can sign up for the VBA classes only, or choose a package that includes Excel School, and you could even add the Excel Dashboards class, and become an Excel master at your own pace.

The VBA course starts at the beginner level, and there are 13 weeks of classes that are designed to take you to the advanced skill level. Online videos show you the steps, and there are workbooks and code examples to download.

You can see the details, and sign up here:  Chandoo's Excel VBA school


You may also like...

12 Responses

  1. David says:

    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. Abrar Ahmed N says:

    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



  3. Blair says:

    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. Mort says:

    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
    ‘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

    • @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. Mort says:

    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. Mort says:

    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. Mort says:

    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. Mort says:

    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”)
    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. Timothy Hill says:

    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?

Leave a Reply

Your email address will not be published. Required fields are marked *