Code Troubles

[merritts]

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.

Please Login or Register  to view this content.

[Tom Ogilvy]

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

[merritts]

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.

Please Login or Register  to view this content.

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[merritts]

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