Combobox filter on data in another row

[Flitsing]

I have a question about a combobox I use.

It's a personel list and contains multiple field from a worksheet.
It shows Personelnr, Surname, Firstname, Date of birth etc. It also contains the field "Start date" and "End date" This last one is empty when a person is still in service.

I want my combobox to show only the persons that are still in service. I can hide those persons in the sheet, but the Combobox will still show them. How can I fix this?


The rows I used are:
cbxPersoon.RowSource = "Personeel!A5:F500"

The End date is in Column "O"

 

[Andrew Poulsom]

Welcome to the Board.

You will need to use the AddItem method rather than the RowSource property. Example here:

http://www.mrexcel.com/forum/showthread.php?t=108644

 

[Flitsing]

Tnx! It helped me a lot already.

Next problem is that I have two Comboboxes so you can search by name or by personel nr. They update eachother and it works fine.

I added the line
Combobox2.AddItem c.Value
so it will be filled with thesame items.
Problem is that it is a multiple row comboxbox and gives a blank row only.

(the first column is the same as Combobox1 (personellnr) with width "0")
Can you help me with this?

 

[Andrew Poulsom]

Do you mean you have a multicolumn ComboBox?

With ComboBox2
    .AddItem c.Value
    .List(.ListCount - 1, 1) = c.Offset(0, 1).Value
End With

That puts the value of the cell to the right in column 2 of the ComboBox. Adjust the Offset to suit.

 

[Flitsing]

Tnx! I am learning a lot this way

I have the following code:

Dim Sh As Worksheet
    Dim Rng As Range
    Dim c As Range
    Set Sh = Worksheets("Personeel")
    Set Rng = Sh.Range("A5:A" & Sh.Range("A65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    For Each c In Rng
        cbxPersoonsnr.AddItem c.Value
        With cbxPersoon
            .AddItem c.Value
            .List(.ListCount - 1, 1) = c.Offset(0, 1).Value
            .List(.ListCount - 1, 2) = c.Offset(0, 2).Value
            .List(.ListCount - 1, 3) = c.Offset(0, 3).Value
            .List(.ListCount - 1, 4) = c.Offset(0, 4).Value
            .List(.ListCount - 1, 5) = c.Offset(0, 5).Value
        End With
    Next c

It works and the multicolumn combocox cbxPersoon shows multiple columns. My question is probably simple to answer, but how can I configure the column widths?

Before I did it like this.

cbxPersoon.ColumnWidths = "0;80;35;50;40;20"

 

[Flitsing]

Allright, I wasn't paying attention. The code I used already worked for the columnwidths. Sorry for bothering.

But another question popped up: I sorted the sheet by name so a search through Combobox2 is easy (alphabetically). But because of it the IDnumbers are mixed up now. It would be nice to scroll down Combobix1 with following numbers instead of the mixed up ones. I cannot find a thread to fix exactly this. Anyone a solution?

 

[Andrew Poulsom]

Look here:

http://www.dicks-blog.com/archives/2004/05/24/sorting-a-multicolumn-listbox/