Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    I'm attempting to create a data input form in Excel 2007 which will have two combo boxes (cmbMajorComponents & cmbSubComponents) based on tables built with ListObject utilizing Access tables. So far I've successfully created the two Excel tables (Table1 & Table2) and set the first combo box (cmbMajorComponents) RowSource to =TableData!Table1. Table1 is the master table and Table2 a sub-table to it. Using the cmbMajorComponents After_Update event, I'm attempting to set/reset cmbSubComponents.RowSource based on the selection using the following code:

    'Clear any prior filter
    ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2
    'Apply the new filter and set the row source
    ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:=cmbMajorComponents
    cmbSubComponents.RowSource = "=TableData!Table2"

    In stepping through the code, Table2 is being correctly filtered, however cmbSubComponents still displays a complete, unfiltered list. Is there a way to have the filtered Table2 applied?

    Thanks,
    Marty

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The row source ignores any filtering applied to the range, it is simply the range that you specify.
    You could use advanced filter to copy the filtered items to another location and use the copied cells as row source.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    Hans,

    Thanks for the reply and confirmation ... I had hoped I overlooked something which would make the filter work.

    I am trying to create a RowSource for the second combo box, but have not stumbled onto the correct syntax for a multi-column rowsource. The following code gives me the correct values, but all in one column rather than three:

    Sheets(pWrkSht).Select
    Set rngMjrCmpnt = ActiveSheet.Range(pRng)
    rngMjrCmpnt.Select
    Range(Selection, Selection.End(xlDown)).Select

    iRows = Selection.Rows.Count ' - 1

    For i = 1 To iRows
    If ActiveCell.Offset(rowOffset:=i, columnOffset:=1) = pValue Then
    pCombo.AddItem "' " & ActiveCell.Offset(rowOffset:=i, columnOffset:=0) & "'; '" & _
    ActiveCell.Offset(rowOffset:=i, columnOffset:=2) & "'; '" & _
    ActiveCell.Offset(rowOffset:=i, columnOffset:=3) & "';"

    End If
    Next i

    Appreciate any help,
    Marty

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    AddItem is used to add the first column in a row only. You need to use the List or Column property to populate the other columns. For example:

    Code:
      For i = 1 To iRows
    	If ActiveCell.Offset(i, 1) = pValue Then
    	  pCombo.AddItem ActiveCell.Offset(i, 0)
    	  pCombo.List(pCombo.ListCount - 1, 1) = ActiveCell.Offset(i, 2)
    	  pCombo.List(pCombo.ListCount - 1, 2) = ActiveCell.Offset(i, 3)
    	End If
      Next i

  5. #5
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    Thanks!!!

    Marty

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •