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.

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.

PartsInventoryUserForm02 

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

_________________

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

4 comments to Excel UserForm Data Entry Update

  • David

    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

  • Abrar Ahmed N

    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

  • Blair

    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")

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>