Re: filter a combobox

Tech-Archive recommends: Fix windows errors by optimizing your registry

Q1: Yes, Sir!
Q2: Yepp!
The only difference I used was to create an array with the product categories
in an Auto_Open module of the workbook:
With Worksheets("Calculation").ComboBox1
.Clear
.List = PGrp ‘ the array of categories
.LinkedCell = "C3"
End With
I also had to include: Me.ComboBox1.Clear before the For Each …….
Otherwise new items was added to the previous list of products for every new
selection of category.

The CB is populated alright but only the in dropdown-state, there is no
value showing in the “off-focus state”, before or after the selection?!?!?!

Well, the reason for IsError is that the CB2 linked cell B3 will display
#N/A if I write
something in the CB instead of selecting an item in the list.
My original properties for CB2 are:
Boundcolumn: 1
Columncount: 2
ColumnWidths: 0 pt;130 pt
LinkedCell: B3
ListFillRange: PDB ‘The product database defined as a named range.

With this setup I get the CB2 populated with the product names (Col2) that
goes to B3 as CB2.Text and the productcode (Col1) is the CB2.Value that goes
to A10 (through the CB2.Change () procedure). If I write a new product the
CB2.Text will still write to B3 but A10 will go empty. When the calculation
is saved, to another workbook, the cell related to A10 will display “New”
(through formula). (I tried to put Range (“A10”) = “New” in the error-true
part of CB2.Change, but it doesn’t work! A10 is always empty unless a
registered product, with product code, is used)

The products are not indexed, the product codes/numbers are alphanumeric and
sorted only to product code (Col1).

I’m getting more and more confused, bhaah!
Mats


"Dave Peterson" wrote:

> Did you put the code behind the work*** with the comboboxes?
>
> Did you use comboboxes from the control toolbox toolbar?
>
> If you put a breakpoint in the combobox1 change code, can you see why combobox2
> is not be populated?
>
> I'm not sure why iserror(combobox2.value) ever comes into play.
>
> Mats Samson wrote:
> >
> > Thank you Dave,
> > it works quite fine the way I set it up for you, but with a small
> > discrepancy plus that I run into another problem!
> > The Product CB never shows any values, only when you push the drop-down list
> > so you can select the items, that’s the discrepancy!
> >
> > In my original setup I had the following piece of code for CB2:
> > Private Sub ComboBox2_Change()
> > If (IsError(ComboBox2.Value)) Then
> > Range("B3") = ComboBox2.Text
> > Else
> > Range("B3") = ComboBox2.Text
> > Range("A10") = ComboBox2.Value
> > End If
> > End Sub
> > The reason for this part is that I want to be able to write a product that
> > is not listed
> > in the product database. Usually the cell A10 gets the product number and
> > it’s used for further processing, to orders and invoices etc. But if I just
> > want to calculate for a product we might be doing business with in the
> > future, I don’t want to register it. I only save the calculation so I can
> > retrieve it again later. For that I need some reference, i.e. the
> > “unregistered” product name. So I had to put in this piece of code to get the
> > other cells to display the product name correctly at any time, otherwise it
> > will display, #N/A.
> > So the question is, can they be combined?
> > Best regards
> > Mats
> >
> > "Dave Peterson" wrote:
> >
> > > This goes behind the ThisWorkbook Module:
> > >
> > > Option Explicit
> > > Private Sub Workbook_Open()
> > > With Worksheets("Calculation").ComboBox1
> > > .Clear
> > > .AddItem "category1"
> > > .AddItem "category2"
> > > .AddItem "category3"
> > > .AddItem "category4"
> > > End With
> > > End Sub
> > >
> > > Change those category# to what you need--and add as many as you need.
> > >
> > > Then put this behind the Calculation work***:
> > >
> > > Option Explicit
> > > Private Sub ComboBox1_Change()
> > >
> > > Dim myRng As Range
> > > Dim myCell As Range
> > >
> > > If Me.ComboBox1.ListIndex < 0 Then
> > > Me.ComboBox2.ListIndex = -1
> > > End If
> > >
> > > With Worksheets("Products")
> > > Set myRng = .Range("c1", .Cells(.Rows.Count, "c").End(xlUp))
> > > End With
> > >
> > > For Each myCell In myRng.Cells
> > > If LCase(myCell.Value) = LCase(Me.ComboBox1.Value) Then
> > > Me.ComboBox2.AddItem myCell.Offset(0, -2).Value
> > > End If
> > > Next myCell
> > >
> > > End Sub
> > >
> > > =====
> > > This line took the value from column A:
> > > Me.ComboBox2.AddItem myCell.Offset(0, -2).Value
> > > If you wanted the value from column B:
> > > Me.ComboBox2.AddItem myCell.Offset(0, -1).Value
> > >
> > >
> > >
> > >
> > > Mats Samson wrote:
> > > >
> > > > Hi Dave,
> > > > I’m sorry but I was travelling a lot and didn’t have any time to go through
> > > > your proposal until now, but I don’t understand how to apply it in my case.
> > > >
> > > > I have a *** named Calculation where I have the two CB’s, one for the
> > > > product names and one for the product categories. In the *** Products, I
> > > > have my small product database where column A is my product number (NOT an
> > > > index number), column B is the product name and column C is the category
> > > > (Plastics, Fillers, Solvents…..). There are more columns but not important
> > > > for this question.
> > > > There are about 200 products belonging to one of 6 categories and as you
> > > > understand when I select the product, I want to limit the list to the
> > > > selected category, so I don’t need to scroll the entire 200 products list.
> > > > Can you explain how to accomplish this?
> > > > Thanks
> > > > Mats
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > Instead of this portion:
> > > > > If Me.ComboBox1.ListIndex < 0 Then
> > > > > Me.ComboBox2.ListIndex = -1
> > > > > End If
> > > > >
> > > > > Try using:
> > > > > Me.ComboBox1.clear
> > > > >
> > > > > Dave Peterson wrote:
> > > > > >
> > > > > > I put two comboboxes from the control toolbox toolbar on a work***.
> > > > > >
> > > > > > I put this code behind the ThisWorkbook module to populate the first combobox
> > > > > > whenever the workbook is opened:
> > > > > >
> > > > > > Option Explicit
> > > > > > Private Sub Workbook_Open()
> > > > > > With Worksheets("sheet1").ComboBox1
> > > > > > .Clear
> > > > > > .AddItem "A"
> > > > > > .AddItem "B"
> > > > > > .AddItem "C"
> > > > > > .AddItem "D"
> > > > > > End With
> > > > > > End Sub
> > > > > >
> > > > > > Then I put this code behind the work*** that contained the two comboboxes:
> > > > > >
> > > > > > Option Explicit
> > > > > > Private Sub ComboBox1_Change()
> > > > > >
> > > > > > Dim myRng As Range
> > > > > > Dim myCell As Range
> > > > > >
> > > > > > If Me.ComboBox1.ListIndex < 0 Then
> > > > > > Me.ComboBox2.ListIndex = -1
> > > > > > End If
> > > > > >
> > > > > > With Worksheets("sheet1")
> > > > > > Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
> > > > > > End With
> > > > > >
> > > > > > For Each myCell In myRng.Cells
> > > > > > If LCase(myCell.Value) = LCase(Me.ComboBox1.Value) Then
> > > > > > Me.ComboBox2.AddItem myCell.Offset(0, 1).Value
> > > > > > End If
> > > > > > Next myCell
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > > I matched up on column A and took the value from column B.
> > > > > >
> > > > > > If you're new to macros, you may want to read David McRitchie's intro at:
> > > > > > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> > > > > >
> > > > > > Mats Samson wrote:
> > > > > > >
> > > > > > > Hello,
> > > > > > > can anyone tell me how to populate a combobox in a *** with records
> > > > > > > meeting a criteria set in another combobox, i.e. show only the records
> > > > > > > (productnames)belonging to the productgroup I’ve selected? I've a small
> > > > > > > product database in Excel.
> > > > > > > Thanks
> > > > > > > Mats
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>
.

Quantcast