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 Ive selected? I've a small
product database in Excel.
Thanks
Mats

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 Ive selected? I've a small
product database in Excel.
Thanks
Mats

--

Dave Peterson

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 Ive selected? I've a small
product database in Excel.
Thanks
Mats

--

Dave Peterson

--

Dave Peterson

Hi Dave,
Im sorry but I was travelling a lot and didnt have any time to go through
your proposal until now, but I dont understand how to apply it in my case.

I have a sheet named Calculation where I have the two CBs, 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 dont 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

Mats,
Take a look here at a great tutorial of what you want to do:
http://www.contextures.com/xlDataVal02.html

HTH,
Jim

"Mats Samson" wrote in message
...
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?Tve selected? I've a
small
product database in Excel.
Thanks
Mats

--

Dave Peterson

--

Dave Peterson

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,
Im sorry but I was travelling a lot and didnt have any time to go through
your proposal until now, but I dont understand how to apply it in my case.

I have a sheet named Calculation where I have the two CBs, 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 dont 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

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, thats 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
its 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 dont 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

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, thats 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
its 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 dont 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

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 doesnt 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).

Im 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

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 doesnt 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).

Im 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

Hello Jim,
thank you for the tip but as you see by the correspondence below,
I don't think my case can be solved with named lists.
Best regards
Mats

"Jim May" wrote:

Mats,
Take a look here at a great tutorial of what you want to do:
http://www.contextures.com/xlDataVal02.html

HTH,
Jim

"Mats Samson" wrote in message
...
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â?Tve selected? I've a
small
product database in Excel.
Thanks
Mats

--

Dave Peterson

--

Dave Peterson

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

Why not use a formula to return the product code?

Put this in A10:

=if(b3="","",index(products!b:b,match(b3,products! c:c,0))

When you populuate B3 with the product name, the formula will evaluate to the
product code.

==========
Alternatively...

I put this 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

With Worksheets("Calculation").ComboBox2
.Boundcolumn = 1
.Columncount = 2
.ColumnWidths = "0 pt;130 pt"
End With

End Sub

I put this behind the calculation worksheet module:

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

With Me.ComboBox2
.Clear
For Each myCell In myRng.Cells
If LCase(myCell.Value) = LCase(Me.ComboBox1.Value) Then
.AddItem myCell.Offset(0, -2).Value
.List(.ListCount - 1, 1) = myCell.Offset(0, -1).Value
End If
Next myCell
End With

End Sub
Private Sub ComboBox2_Change()

With Me.ComboBox2
If .ListIndex < 0 Then
Me.Range("b3,a10").ClearContents
Else
Me.Range("b3").Value = .List(.ListIndex, 0)
Me.Range("a10").Value = .List(.ListIndex, 1)
End If
End With

End Sub

I'm not sure if I got the product code/item number in the right position. But
that shouldn't be too difficult to fix.

Notice that I did add a .clear in the combobox1_change event. No matter which
routine (today's or yesterday's), I would think that this would be a good thing
to have.


Mats Samson wrote:

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


--

Dave Peterson

Thats exactly what I dont want to do!
The reason is that column A and B are used to put all the conditions for the
business calculation, deliveries, quantity, purchase price, currencies,
freight, insurances, etc, etc,€¦..A calculation contains parameters of
different kind in nearly all cells in the range A3:B28. The actual
calculation is taking place in columns F to J. The Column A and B VALUES are
saved in a €œregister€� and because there are so many parameters they are saved
in two lines to be readable when you look into the saved calculations list.
I can retrieve an old calculation by copying it back to the calculation
sheet, transposing 2 lines back to 2 columns and pasting VALUES only.
Any formula in A10 will be destroyed by a retrieved calculation.
Accordingly the A10 value has to be set by a function/code, not a formula.

Regards
Mats


"Dave Peterson" wrote:

Why not use a formula to return the product code?

Put this in A10:

=if(b3="","",index(products!b:b,match(b3,products! c:c,0))

When you populuate B3 with the product name, the formula will evaluate to the
product code.

==========
Alternatively...

I put this 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

With Worksheets("Calculation").ComboBox2
.Boundcolumn = 1
.Columncount = 2
.ColumnWidths = "0 pt;130 pt"
End With

End Sub

I put this behind the calculation worksheet module:

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

With Me.ComboBox2
.Clear
For Each myCell In myRng.Cells
If LCase(myCell.Value) = LCase(Me.ComboBox1.Value) Then
.AddItem myCell.Offset(0, -2).Value
.List(.ListCount - 1, 1) = myCell.Offset(0, -1).Value
End If
Next myCell
End With

End Sub
Private Sub ComboBox2_Change()

With Me.ComboBox2
If .ListIndex < 0 Then
Me.Range("b3,a10").ClearContents
Else
Me.Range("b3").Value = .List(.ListIndex, 0)
Me.Range("a10").Value = .List(.ListIndex, 1)
End If
End With

End Sub

I'm not sure if I got the product code/item number in the right position. But
that shouldn't be too difficult to fix.

Notice that I did add a .clear in the combobox1_change event. No matter which
routine (today's or yesterday's), I would think that this would be a good thing
to have.


Mats Samson wrote:

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 won€„¢t
work at all!
But the reason why it didn€„¢t 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.

So the code didn't work either.

I guess I don't understand what you want.

Sorry.

Mats Samson wrote:

Thats exactly what I dont want to do!
The reason is that column A and B are used to put all the conditions for the
business calculation, deliveries, quantity, purchase price, currencies,
freight, insurances, etc, etc,€¦..A calculation contains parameters of
different kind in nearly all cells in the range A3:B28. The actual
calculation is taking place in columns F to J. The Column A and B VALUES are
saved in a €œregister€� and because there are so many parameters they are saved
in two lines to be readable when you look into the saved calculations list.
I can retrieve an old calculation by copying it back to the calculation
sheet, transposing 2 lines back to 2 columns and pasting VALUES only.
Any formula in A10 will be destroyed by a retrieved calculation.
Accordingly the A10 value has to be set by a function/code, not a formula.

Regards
Mats

"Dave Peterson" wrote:

Why not use a formula to return the product code?

Put this in A10:

=if(b3="","",index(products!b:b,match(b3,products! c:c,0))

When you populuate B3 with the product name, the formula will evaluate to the
product code.

==========
Alternatively...

I put this 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

With Worksheets("Calculation").ComboBox2
.Boundcolumn = 1
.Columncount = 2
.ColumnWidths = "0 pt;130 pt"
End With

End Sub

I put this behind the calculation worksheet module:

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

With Me.ComboBox2
.Clear
For Each myCell In myRng.Cells
If LCase(myCell.Value) = LCase(Me.ComboBox1.Value) Then
.AddItem myCell.Offset(0, -2).Value
.List(.ListCount - 1, 1) = myCell.Offset(0, -1).Value
End If
Next myCell
End With

End Sub
Private Sub ComboBox2_Change()

With Me.ComboBox2
If .ListIndex < 0 Then
Me.Range("b3,a10").ClearContents
Else
Me.Range("b3").Value = .List(.ListIndex, 0)
Me.Range("a10").Value = .List(.ListIndex, 1)
End If
End With

End Sub

I'm not sure if I got the product code/item number in the right position. But
that shouldn't be too difficult to fix.

Notice that I did add a .clear in the combobox1_change event. No matter which
routine (today's or yesterday's), I would think that this would be a good thing
to have.


Mats Samson wrote:

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 won€„¢t
work at all!
But the reason why it didn€„¢t 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.

--

Dave Peterson

Oh yes, it works! If you mean the code discussion, yes perfectly alright!
But the formula doesnt, as explained above.
The remaining problem is that I want the product code as well, at the same
time as I choose the product. The product codes you can design so the
connected product comes in blocks, in my case supplier wise. Its simple
database handling, whether its
products, customer or suppliers, you need a unique common denominator.
We created a new problem when extracting a list out of the product db, we
lost touch with the €œindex value€�, the product code. Still, I want the
product code as well, for the other formulas to work correctly. If you need
further explanation, Id better send you my files so you can see what I want
to achieve. In such case, please send me an email address where I can reach
you.
Best regards
Mats


"Dave Peterson" wrote:

So the code didn't work either.

I guess I don't understand what you want.

Sorry.

Mats Samson wrote:

That€„¢s exactly what I don€„¢t want to do!
The reason is that column A and B are used to put all the conditions for the
business calculation, deliveries, quantity, purchase price, currencies,
freight, insurances, etc, etc,€¦..A calculation contains parameters of
different kind in nearly all cells in the range A3:B28. The actual
calculation is taking place in columns F to J. The Column A and B VALUES are
saved in a €œregister€� and because there are so many parameters they are saved
in two lines to be readable when you look into the saved calculation€„¢s list.
I can retrieve an old calculation by copying it back to the calculation
sheet, transposing 2 lines back to 2 columns and pasting VALUES only.
Any formula in A10 will be destroyed by a retrieved calculation.
Accordingly the A10 value has to be set by a function/code, not a formula.

Regards
Mats

"Dave Peterson" wrote:

Why not use a formula to return the product code?

Put this in A10:

=if(b3="","",index(products!b:b,match(b3,products! c:c,0))

When you populuate B3 with the product name, the formula will evaluate to the
product code.

==========
Alternatively...

I put this 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

With Worksheets("Calculation").ComboBox2
.Boundcolumn = 1
.Columncount = 2
.ColumnWidths = "0 pt;130 pt"
End With

End Sub

I put this behind the calculation worksheet module:

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

With Me.ComboBox2
.Clear
For Each myCell In myRng.Cells
If LCase(myCell.Value) = LCase(Me.ComboBox1.Value) Then
.AddItem myCell.Offset(0, -2).Value
.List(.ListCount - 1, 1) = myCell.Offset(0, -1).Value
End If
Next myCell
End With

End Sub
Private Sub ComboBox2_Change()

With Me.ComboBox2
If .ListIndex < 0 Then
Me.Range("b3,a10").ClearContents
Else
Me.Range("b3").Value = .List(.ListIndex, 0)
Me.Range("a10").Value = .List(.ListIndex, 1)
End If
End With

End Sub

I'm not sure if I got the product code/item number in the right position. But
that shouldn't be too difficult to fix.

Notice that I did add a .clear in the combobox1_change event. No matter which
routine (today's or yesterday's), I would think that this would be a good thing
to have.


Mats Samson wrote:

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 won€„¢t
work at all!
But the reason why it didn€„¢t 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 don't open attachments or workbooks from others.

I thought that the code populated both the product description and product code.



Mats Samson wrote:

Oh yes, it works! If you mean the code discussion, yes perfectly alright!
But the formula doesnt, as explained above.
The remaining problem is that I want the product code as well, at the same
time as I choose the product. The product codes you can design so the
connected product comes in blocks, in my case supplier wise. Its simple
database handling, whether its
products, customer or suppliers, you need a unique common denominator.
We created a new problem when extracting a list out of the product db, we
lost touch with the €œindex value€�, the product code. Still, I want the
product code as well, for the other formulas to work correctly. If you need
further explanation, Id better send you my files so you can see what I want
to achieve. In such case, please send me an email address where I can reach
you.
Best regards
Mats

OK!
But I think I can use a HLookup formula to get the leftmost cell-value in
the row of the selected CB2-value.
If you have any ideas youre heartily welcome!
Regards
Mats


"Dave Peterson" wrote:

I don't open attachments or workbooks from others.

I thought that the code populated both the product description and product code.



Mats Samson wrote:

Oh yes, it works! If you mean the code discussion, yes perfectly alright!
But the formula doesn€„¢t, as explained above.
The remaining problem is that I want the product code as well, at the same
time as I choose the product. The product codes you can design so the
connected product comes in blocks, in my case supplier wise. It€„¢s simple
database handling, whether it€„¢s
products, customer or suppliers, you need a unique common denominator.
We created a new problem when extracting a list out of the product db, we
lost touch with the €œindex value€�, the product code. Still, I want the
product code as well, for the other formulas to work correctly. If you need
further explanation, I€„¢d better send you my files so you can see what I want
to achieve. In such case, please send me an email address where I can reach
you.
Best regards
Mats

Depending on how many fields are used, why not just keep adding them to the
combobox list (but hide them). That's what that code did.

Mats Samson wrote:

OK!
But I think I can use a HLookup formula to get the leftmost cell-value in
the row of the selected CB2-value.
If you have any ideas youre heartily welcome!
Regards
Mats

"Dave Peterson" wrote:

I don't open attachments or workbooks from others.

I thought that the code populated both the product description and product code.



Mats Samson wrote:

Oh yes, it works! If you mean the code discussion, yes perfectly alright!
But the formula doesn€„¢t, as explained above.
The remaining problem is that I want the product code as well, at the same
time as I choose the product. The product codes you can design so the
connected product comes in blocks, in my case supplier wise. It€„¢s simple
database handling, whether it€„¢s
products, customer or suppliers, you need a unique common denominator.
We created a new problem when extracting a list out of the product db, we
lost touch with the €œindex value€�, the product code. Still, I want the
product code as well, for the other formulas to work correctly. If you need
further explanation, I€„¢d better send you my files so you can see what I want
to achieve. In such case, please send me an email address where I can reach
you.
Best regards
Mats



--

Dave Peterson

Dave!
I found the solution in your suggested formula:
myPC = Application.Index(myWS.Range("a:a"), Application.Match(myPN,
myWS.Range("b:b"), 0))
myPC refers to cell A10 of course.

Thanks a mille!
Mats

"Dave Peterson" wrote:

Depending on how many fields are used, why not just keep adding them to the
combobox list (but hide them). That's what that code did.

Mats Samson wrote:

OK!
But I think I can use a HLookup formula to get the leftmost cell-value in
the row of the selected CB2-value.
If you have any ideas you€„¢re heartily welcome!
Regards
Mats

"Dave Peterson" wrote:

I don't open attachments or workbooks from others.

I thought that the code populated both the product description and product code.



Mats Samson wrote:

Oh yes, it works! If you mean the code discussion, yes perfectly alright!
But the formula doesn€„¢t, as explained above.
The remaining problem is that I want the product code as well, at the same
time as I choose the product. The product codes you can design so the
connected product comes in blocks, in my case supplier wise. It€„¢s simple
database handling, whether it€„¢s
products, customer or suppliers, you need a unique common denominator.
We created a new problem when extracting a list out of the product db, we
lost touch with the €ŀœindex value€ÂÂ�, the product code. Still, I want the
product code as well, for the other formulas to work correctly. If you need
further explanation, I€„¢d better send you my files so you can see what I want
to achieve. In such case, please send me an email address where I can reach
you.
Best regards
Mats



--

Dave Peterson