Hi Dave,
sorry, I forgot to mention that I already dropped the ListFillRange and the
other properties of the CB. It was of course necessary, otherwise it wont
work at all!
But the reason why it didnt work was that I forgot to remove the TextColumn
property that was set to 2. So now it works OK and CB2 displays the selected
item.
This TextColumn setting belonged to my original setup through which I got 2
€œvalues€� out of one CB. The product name as the Text property and the Product
code as the Value property at the same time it served as index for the other
values in a record. Then I could look up other values of the particular
record, like supplier and units, etc. and present them in my calculation)
The only remaining problem is that since we kind of removed the €œconnection€�
to the
product database and only creating a list based on a criteria, I cannot get
the product code into cell A10. It must not be a formula in the cell, only
the value.
Can we do that? When I select the product name (ColB) that goes to B3 I get
at the same time the Product code from ColA going into A10?

I would appreciate your help very much!
Best regards
Mats


"Dave Peterson" wrote:

This code in the ThisWorkbook module is used instead of the code in your
auto_open procedu

Option Explicit
Private Sub Workbook_Open()
With Worksheets("Calculation").ComboBox1
.Clear
.AddItem "category1"
.AddItem "category2"
.AddItem "category3"
.AddItem "category4"
End With
End Sub


Pick one and use that.

Also, try dropping the linked cell and assigning the value directly in code.

And get rid of those properties in the combobox2. Instead of using
listfillrange, the code is looping through the list to get the items to display
in that combobox.



Mats Samson wrote:

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 a
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 worksheet 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 worksheet:

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 sheet named Calculation where I have the two CB€„¢s, one for the
product names and one for the product categories. In the sheet 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 worksheet.

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


--

Dave Peterson