ComboBox list reliant on the entry from a different ComboBox

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > >
> > >
> > >
>
>
>