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

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

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