![]()
Private Sub cmdAdd_Click() Dim lRow As Long Dim lPart As Long Dim ws As Worksheet Set ws = Worksheets("PartsData") 'find first empty row in database ''lRow = ws.Cells(Rows.Count, 1) _ '' .End(xlUp).Offset(1, 0).Row 'revised code to avoid problems with 'Excel lists and tables in newer versions lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1 lPart = Me.cboPart.ListIndex 'check for a part number If Trim(Me.cboPart.Value) = "" Then Me.cboPart.SetFocus MsgBox "Please enter a part number" End If If Trim(Me.cboLocation.Value) = "" Then Me.cboLocation.SetFocus MsgBox "Please enter the Location" Exit Sub End If 'copy the data to the database With ws .Cells(lRow, 1).Value = Me.txtDate.Value .Cells(lRow, 2).Value = Me.cboLocation.Value .Cells(lRow, 4).Value = Me.cboPart.Value .Cells(lRow, 5).Value = Me.cboPart.List(lPart, 1) .Cells(lRow, 9).Value = Me.txtQty.Value .Cells(lRow, 11).Value = Me.txtQty2.Value .Cells(lRow, 7).Value = Me.txtQty3.Value .Cells(lRow, 8).Value = Me.txtQty4.Value 'new addition End With 'clear the data Me.cboPart.Value = "" Me.cboLocation.Value = "" Me.cboLocation2.Value = "" Me.txtDate.Value = Format(Date, "Medium Date") Me.txtQty.Value = "" Me.cboPart.SetFocus End Sub
Bookmarks