Tom, thanks for all your help!
The code doesnt seem to work. I select an entry in one combo box and
the others remain blank and the desired fields are never filled. I was
wondering how vba handles calling AutoFill()? Or if my code would even
do what i was hoping it to do (i am pretty new to vba so i am going
mostly off a couple of books i bought). Thanks again for all your
help!
Also, i changed the ws range to
Set DesiredColumn = ws.Range("DescriptionList")
And here is my code in full, if it helps at all.
Code:
--------------------
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("zinvrep")
'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'lPart = Me.txtPart.ListIndex
'check for a part number
'If Trim(txtPart.Value) = "" Then
' Me.txtPart.SetFocus
'MsgBox "Please enter a part number"
'Exit Sub
'End If
'check for a quantity number
If Trim(txtQty.Value) = "" Then
Me.txtQty.SetFocus
MsgBox "Please enter a quantity"
Exit Sub
End If
'check for a Description number
If Trim(cboDescription.Value) = "" Then
Me.txtQty.SetFocus
MsgBox "Please select a description"
Exit Sub
End If
'copy the data to the database
With ws
.Cells(lRow, 1).Value = cboPartType.Value
.Cells(lRow, 2).Value = cboPartClass.Value
.Cells(lRow, 3).Value = cboDescription.Value
.Cells(lRow, 6).Value = cboWarehouse.Value
.Cells(lRow, 7).Value = cboLocation.Value
.Cells(lRow, 8).Value = cboManufacturer.Value
.Cells(lRow, 9).Value = cboMfgrNumber.Value
.Cells(lRow, 12).Value = txtQty.Value
.Cells(lRow, 13).Value = txtPCBRef.Value
'.Cells(lRow, 4).Value = txtPart.Value
.Cells(lRow, 4).Value = cboPacNumber.Value
End With
'clear the data
cboPartType.Value = ""
cboPartClass.Value = ""
cboDescription.Value = ""
cboWarehouse.Value = ""
cboLocation.Value = ""
cboManufacturer.Value = ""
cboMfgrNumber.Value = ""
txtQty.Value = ""
txtPCBRef.Value = ""
'txtPart.Value = ""
cboPacNumber.Value = ""
End Sub
Private Sub Label1_Click()
End Sub
'assures that only numbers are input into quantity
Private Sub txtQty_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case Asc("0") To Asc("9")
Case Asc("-")
If InStr(1, txtQty.Text, "-") > 0 Or txtQty.SelStart > 0 Then
KeyAscii = 0
End If
Case Asc(".")
If InStr(1, txtQty.Text, ".") > 0 Then
KeyAscii = 0
End If
Case Else
KeyAscii = 0
MsgBox ("Quantity must be numeric")
End Select
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim cPartType As Range
Dim cLoc As Range
Dim cPartClass As Range
Dim cWhouse As Range
Dim cDescription As Range
Dim cMfgrNumber As Range
Dim cPacNumber As Range
Dim ws As Worksheet
Set ws = Worksheets("LookUpLists")
For Each cPacNumber In ws.Range("PacNumberList")
With cboPacNumber
.AddItem cPacNumber.Value
End With
Next cPacNumber
For Each cPartType In ws.Range("PartTypeList")
With cboPartType
.AddItem cPartType.Value
End With
Next cPartType
For Each cLoc In ws.Range("LocationList")
With cboLocation
.AddItem cLoc.Value
End With
Next cLoc
For Each cMfgrNumber In ws.Range("MfgrNumberList")
With cboMfgrNumber
.AddItem cMfgrNumber.Value
End With
Next cMfgrNumber
For Each cPartClass In ws.Range("PartClassList")
With cboPartClass
.AddItem cPartClass.Value
End With
Next cPartClass
For Each cWarehouse In ws.Range("WarehouseList")
With cboWarehouse
.AddItem cWarehouse.Value
End With
Next cWarehouse
For Each cLoc In ws.Range("ManufacturerList")
With cboManufacturer
.AddItem cLoc.Value
End With
Next cLoc
For Each cDescription In ws.Range("DescriptionList")
With cboDescription
.AddItem cDescription.Value
End With
Next cDescription
End Sub
Public Sub AutoFill()
Dim DesiredColumn As Range
Dim FoundRow As Range
Dim RowNumber As Long
Dim ws As Worksheet
Set ws = Worksheets("LookUpLists")
'Specifies the desired column as the Description Column
Set DesiredColumn = ws.Range("DescriptionList")
Set FoundRow = DesiredColumn.Find( _
What:=cboDescription.Value, _
After:=Range("f2"), _
Lookin:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not foundrow is nothing then
RowNumber = FoundRow.Row
cboMfgrNumber.Value = ws.Cells(RowNumber, 7).Value
cboPacNumber.Value = ws.Cells(RowNumber, 8).Value
End If
End Sub
