1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Add data from User Form to first empty row

Discussion in 'Business Applications' started by peanutsgang, Jan 20, 2011.

Thread Status:
Not open for further replies.
  1. peanutsgang

    peanutsgang Thread Starter

    Jan 20, 2011
    I know that there is previous thread along this same line, but I have a different issue. I am a beginner with VBA and have been using examples from the Internet to write my codes. I have a User Form that I created in Excel 2007 that adds the information entered to the first empty row without any problems. Since then, I have been creating a new User Form using the same code, however, I'm getting the "Application-defined or object-defined error". Debugging takes me to the code for finding the first empty row.

    Here is my code:

    Private Sub cmdSubmit_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Inventory Request Log")
    'check user input
    If Me.txtName.Value = "" Then
    MsgBox "Please enter your full name.", vbExclamation, "Inventory Request"
    Exit Sub
    End If
    If Me.txtPhone.Value = "" Then
    MsgBox "Please enter Phone Number.", vbExclamation, "Inventory Request"
    Exit Sub
    End If
    If Me.cboCampus.Value = "" Then
    MsgBox "Please select the Campus item is located.", vbExclamation, "Inventory Request"
    Exit Sub
    End If
    If Me.cboProdReq.Value = "" Then
    MsgBox "Please select Type of Request.", vbExclamation, "Inventory Request"
    Exit Sub
    End If
    If Me.cboType.Value = "" Then
    MsgBox "Please select product type.", vbExclamation, "Inventory Request"
    Exit Sub
    End If
    If Me.cboCase.Value = "" Then
    MsgBox "Must indicate if needed for a specific case.", vbExclamation, "Inventory Request"
    Exit Sub
    End If
    If Me.cboLoaner.Value = "" Then
    MsgBox "Must indicate if a Loaner item is required.", vbExclamation, "Inventory Request"
    Exit Sub
    End If
    If Me.txtItemnumber.Value = "" Then
    MsgBox "Please enter the Item Number.", vbExclamation, "Inventory Request"
    Exit Sub
    End If
    'find first empty row in database
    iRow = ws.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row
    'copy the data to the database
    ws.Cells(iRow, 1).Value = Me.txtName.Value
    ws.Cells(iRow, 2).Value = Me.txtDate.Value
    ws.Cells(iRow, 3).Value = Me.txtPhone.Value
    ws.Cells(iRow, 4).Value = Me.txtEmail.Value
    ws.Cells(iRow, 5).Value = Me.cboCampus.Value
    ws.Cells(iRow, 6).Value = Me.cboProdReq.Value
    ws.Cells(iRow, 7).Value = Me.cboType.Value
    ws.Cells(iRow, 8).Value = Me.cboLoaner.Value
    ws.Cells(iRow, 9).Value = Me.cboCase.Value
    ws.Cells(iRow, 10).Value = Me.txtCasedate.Value
    ws.Cells(iRow, 11).Value = Me.txtVendor.Value
    ws.Cells(iRow, 12).Value = Me.txtItemnumber.Value
    ws.Cells(iRow, 13).Value = Me.txtQuantity.Value
    ws.Cells(iRow, 14).Value = Me.cboUnit.Value
    ws.Cells(iRow, 15).Value = Me.txtComments.Value
    'clear the data
    Me.txtName.Value = ""
    Me.txtPhone.Value = ""
    Me.txtEmail.Value = ""
    Me.cboCampus.Value = ""
    Me.cboProdReq.Value = ""
    Me.cboType.Value = ""
    Me.cboLoaner.Value = ""
    Me.cboCase.Value = ""
    Me.txtCasedate.Value = ""
    Me.txtVendor.Value = ""
    Me.txtItemnumber.Value = ""
    Me.txtQuantity.Value = ""
    Me.cboUnit.Value = ""
    Me.txtComments.Value = ""

    End Sub

    Private Sub UserForm_Initialize()
    Dim cProdReq As Range
    Dim cType As Range
    Dim cMeasure As Range
    Dim cCase As Range
    Dim cCampus As Range
    Dim cLoaner As Range
    Dim ws As Worksheet
    Set ws = Worksheets("LookupLists")
    For Each cProdReq In ws.Range("ProductList")
    With Me.cboProdReq
    .AddItem cProdReq.Value
    .List(.ListCount - 1, 1) = cProdReq.Offset(0, 1).Value
    End With
    Next cProdReq
    For Each cMeasure In ws.Range("UnitList")
    With Me.cboUnit
    .AddItem cMeasure.Value
    End With
    Next cMeasure
    For Each cType In ws.Range("TypeList")
    With Me.cboType
    .AddItem cType.Value
    End With
    Next cType
    For Each cCase In ws.Range("CaseList")
    With Me.cboCase
    .AddItem cCase.Value
    End With
    Next cCase
    For Each cCampus In ws.Range("CampusList")
    With Me.cboCampus
    .AddItem cCampus.Value
    End With
    Next cCampus
    For Each cLoaner In ws.Range("LoanerList")
    With Me.cboLoaner
    .AddItem cLoaner.Value
    End With
    Next cLoaner
    Me.txtDate.Value = Format(Date, "Short Date")
    End Sub

    This is the same code I used before, just with different field and label names. Can anyone help me understand why it is not working this time? What do I need to fix?

    I think I have been looking at it too long and am blind to the problem.

    Thank you.
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/975884

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice