Add data from User Form to first empty row

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

peanutsgang

Thread Starter
Joined
Jan 20, 2011
Messages
1
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"
Me.txtName.SetFocus
Exit Sub
End If
If Me.txtPhone.Value = "" Then
MsgBox "Please enter Phone Number.", vbExclamation, "Inventory Request"
Me.txtPhone.SetFocus
Exit Sub
End If
If Me.cboCampus.Value = "" Then
MsgBox "Please select the Campus item is located.", vbExclamation, "Inventory Request"
Me.cboCampus.SetFocus
Exit Sub
End If
If Me.cboProdReq.Value = "" Then
MsgBox "Please select Type of Request.", vbExclamation, "Inventory Request"
Me.cboProdReq.SetFocus
Exit Sub
End If
If Me.cboType.Value = "" Then
MsgBox "Please select product type.", vbExclamation, "Inventory Request"
Me.cboType.SetFocus
Exit Sub
End If
If Me.cboCase.Value = "" Then
MsgBox "Must indicate if needed for a specific case.", vbExclamation, "Inventory Request"
Me.cboCase.SetFocus
Exit Sub
End If
If Me.cboLoaner.Value = "" Then
MsgBox "Must indicate if a Loaner item is required.", vbExclamation, "Inventory Request"
Me.cboLoaner.SetFocus
Exit Sub
End If
If Me.txtItemnumber.Value = "" Then
MsgBox "Please enter the Item Number.", vbExclamation, "Inventory Request"
Me.txtItemnumber.SetFocus
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
ThisWorkbook.Save
'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 = ""
Me.txtName.SetFocus


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")
Me.txtName.SetFocus
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top