I have a UserForm with 2 different ComboBoxes. The first picks up a list of
countries from sheet2. The second will give a list of customers from the
country selected from box 1.

The list of customer names is in one list on sheet2 containing all customers
from each country. The country name is included along side the customer name.

I have tried for hours to get this right but nothing seems to work, please
help.

Private Sub Combobox1_Click()
Dim sCountry as String
sCountry = Combobox1.Value
Combobox2.RowSource = ""
Combobox2.Clear
for each cell in Worksheets("Sheet2").Range("A2:A50")
if lcase(cell.offset(0,1).Value) = lcase(sCountry) then
Combobox2.AddItem cell.Value
end if
Next
End Sub

--
Regards,
Tom Ogilvy

"ndm berry" wrote in message
...
I have a UserForm with 2 different ComboBoxes. The first picks up a list
of
countries from sheet2. The second will give a list of customers from the
country selected from box 1.

The list of customer names is in one list on sheet2 containing all
customers
from each country. The country name is included along side the customer
name.

I have tried for hours to get this right but nothing seems to work, please
help.

Thanks for the help but unfortunately I didn't give enough information before.

The code works but I need the customer number AND name in ComboBox2 (so 2
columns required) and the list of customers is dynamic so the range of the
list will be constantly changing.

"Tom Ogilvy" wrote:

Private Sub Combobox1_Click()
Dim sCountry as String
sCountry = Combobox1.Value
Combobox2.RowSource = ""
Combobox2.Clear
for each cell in Worksheets("Sheet2").Range("A2:A50")
if lcase(cell.offset(0,1).Value) = lcase(sCountry) then
Combobox2.AddItem cell.Value
end if
Next
End Sub

--
Regards,
Tom Ogilvy

"ndm berry" wrote in message
...
I have a UserForm with 2 different ComboBoxes. The first picks up a list
of
countries from sheet2. The second will give a list of customers from the
country selected from box 1.

The list of customer names is in one list on sheet2 containing all
customers
from each country. The country name is included along side the customer
name.

I have tried for hours to get this right but nothing seems to work, please
help.

Private Sub Combobox1_Click()
Dim sCountry as String
sCountry = Combobox1.Value
Combobox2.RowSource = ""
Combobox2.Clear
Combobox2.ColumnCount = 2
With Worksheets("Sheet2")
set rng = .range(.cells(2,1),.cells(2,1).End(xldown))
End With
for each cell in rng
if lcase(cell.offset(0,1).Value) = lcase(sCountry) then
Combobox2.AddItem cell.Value
combobox2.List(combobox2.Listcount-1,1) = cell.offset(0,2).value
end if
Next
End Sub

Change the references to use the appropriate locations.

I assume
Customer name in A
country in B
customer number in C

all on sheet2
--
Regards,
Tom Ogilvy


"ndm berry" wrote in message
...
Thanks for the help but unfortunately I didn't give enough information
before.

The code works but I need the customer number AND name in ComboBox2 (so 2
columns required) and the list of customers is dynamic so the range of the
list will be constantly changing.

"Tom Ogilvy" wrote:

Private Sub Combobox1_Click()
Dim sCountry as String
sCountry = Combobox1.Value
Combobox2.RowSource = ""
Combobox2.Clear
for each cell in Worksheets("Sheet2").Range("A2:A50")
if lcase(cell.offset(0,1).Value) = lcase(sCountry) then
Combobox2.AddItem cell.Value
end if
Next
End Sub

--
Regards,
Tom Ogilvy

"ndm berry" wrote in message
...
I have a UserForm with 2 different ComboBoxes. The first picks up a
list
of
countries from sheet2. The second will give a list of customers from
the
country selected from box 1.

The list of customer names is in one list on sheet2 containing all
customers
from each country. The country name is included along side the
customer
name.

I have tried for hours to get this right but nothing seems to work,
please
help.

Thank you very much!

"Tom Ogilvy" wrote:

Private Sub Combobox1_Click()
Dim sCountry as String
sCountry = Combobox1.Value
Combobox2.RowSource = ""
Combobox2.Clear
Combobox2.ColumnCount = 2
With Worksheets("Sheet2")
set rng = .range(.cells(2,1),.cells(2,1).End(xldown))
End With
for each cell in rng
if lcase(cell.offset(0,1).Value) = lcase(sCountry) then
Combobox2.AddItem cell.Value
combobox2.List(combobox2.Listcount-1,1) = cell.offset(0,2).value
end if
Next
End Sub

Change the references to use the appropriate locations.

I assume
Customer name in A
country in B
customer number in C

all on sheet2
--
Regards,
Tom Ogilvy


"ndm berry" wrote in message
...
Thanks for the help but unfortunately I didn't give enough information
before.

The code works but I need the customer number AND name in ComboBox2 (so 2
columns required) and the list of customers is dynamic so the range of the
list will be constantly changing.

"Tom Ogilvy" wrote:

Private Sub Combobox1_Click()
Dim sCountry as String
sCountry = Combobox1.Value
Combobox2.RowSource = ""
Combobox2.Clear
for each cell in Worksheets("Sheet2").Range("A2:A50")
if lcase(cell.offset(0,1).Value) = lcase(sCountry) then
Combobox2.AddItem cell.Value
end if
Next
End Sub

--
Regards,
Tom Ogilvy

"ndm berry" wrote in message
...
I have a UserForm with 2 different ComboBoxes. The first picks up a
list
of
countries from sheet2. The second will give a list of customers from
the
country selected from box 1.

The list of customer names is in one list on sheet2 containing all
customers
from each country. The country name is included along side the
customer
name.

I have tried for hours to get this right but nothing seems to work,
please
help.