Hi,

I am attempting to write code that will take a comboBox selection and
then autofill other combo boxes with related selections (in the same
row on a lookuplist). I believe i have the idea correct, but i am
struggling with actual implementation. Any and all comments and
suggestions are greatly appreciated.


Code:
--------------------
Private Sub AutoFill()
Dim DesiredColumn As Range
Dim RoundRow As Range
Dim RowNumber As Range
Dim ws As Worksheet
Set ws = Worksheets("LookUpLists")

'Specifies the desired column as the Description Column
Set DesiredColumn = ws.Range(f2, F1100)

'Searches for user's entry into Description Combobox
Set FoundRow = DesiredColumn.Find(cboDescription.Value, f2, xlValues, , xlByColumns)

'Specifies Row # of users selection
Set RowNumber = FoundRow.Row

'Assigns the values from the accompaning columns to the combo box value
cboMfgrNumber.Value = ws.Cells(RowNumber, 7).Value
cboPacNumber.Value = ws.Cells(RowNumber, 8).Value

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


--
merritts
------------------------------------------------------------------------
merritts's Profile: http://www.excelforum.com/member.php...o&userid=35803
View this thread: http://www.excelforum.com/showthread...hreadid=564838

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("f2:F1100")

'Searches for user's entry into Description Combobox
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

--
regards,
Tom Ogilvy

"merritts" wrote:


Hi,

I am attempting to write code that will take a comboBox selection and
then autofill other combo boxes with related selections (in the same
row on a lookuplist). I believe i have the idea correct, but i am
struggling with actual implementation. Any and all comments and
suggestions are greatly appreciated.


Code:
--------------------
Private Sub AutoFill()
Dim DesiredColumn As Range
Dim RoundRow As Range
Dim RowNumber As Range
Dim ws As Worksheet
Set ws = Worksheets("LookUpLists")

'Specifies the desired column as the Description Column
Set DesiredColumn = ws.Range(f2, F1100)

'Searches for user's entry into Description Combobox
Set FoundRow = DesiredColumn.Find(cboDescription.Value, f2, xlValues, , xlByColumns)

'Specifies Row # of users selection
Set RowNumber = FoundRow.Row

'Assigns the values from the accompaning columns to the combo box value
cboMfgrNumber.Value = ws.Cells(RowNumber, 7).Value
cboPacNumber.Value = ws.Cells(RowNumber, 8).Value

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


--
merritts
------------------------------------------------------------------------
merritts's Profile: http://www.excelforum.com/member.php...o&userid=35803
View this thread: http://www.excelforum.com/showthread...hreadid=564838

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


--
merritts
------------------------------------------------------------------------
merritts's Profile: http://www.excelforum.com/member.php...o&userid=35803
View this thread: http://www.excelforum.com/showthread...hreadid=564838

Excel doesn't just call autofill. You need to call it yourself using the
click event of the combobox

Private Sub cboDescription_Click()
Autofill
End Sub

I would also name it something other than autofill since that is a method of
the range object.

--
Regards,
Tom Ogilvy



"merritts" wrote in
message ...

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


--
merritts
------------------------------------------------------------------------
merritts's Profile:
http://www.excelforum.com/member.php...o&userid=35803
View this thread: http://www.excelforum.com/showthread...hreadid=564838

Works great! Thanks for all your time and effort i truly appreciate it.


--
merritts
------------------------------------------------------------------------
merritts's Profile: http://www.excelforum.com/member.php...o&userid=35803
View this thread: http://www.excelforum.com/showthread...hreadid=564838