Auto-Filling Related Combo Box Entries

[merritts]

Hi,

I have a number of combo boxes in a user form. I would like for those
combo boxes to be related so that when one is completed the respective
data for the others would be auto filled. For example, if the user were
to select manually select the part description the respective part
number, manufacturer, type would be auto filled.

The options for the combo boxes are located on a separate sheet in the
destination workbook (entries are placed in sheet1) and i could place
the related entries in the same row in the lookuplist sheet. Thus, the
parts number, description, manufacturer etc would be in the same row
(that might make it easier?). Below is the code i currently have for my
user form. I am fairly new to this so i am not sure if i am providing
enough information so please let me know if anything is unclear.

Thanks for any and all help!


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

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 ws As Worksheet
Set ws = Worksheets("LookUpLists")


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

For Each cMfgrNumber In ws.Range("MfgrNumberList")
With cboMfgrNumber
.AddItem cMfgrNumber.Value
End With
Next cMfgrNumber

End Sub
--------------------


+-------------------------------------------------------------------+
|Filename: Userform code - 7_24_06.txt |
|Download: http://www.excelforum.com/attachment.php?postid=5085 |
+-------------------------------------------------------------------+