i am using a dropdown list to show product names. when a product is selected
from the list, key information appears in the adjacent columns. what i need
to do now is make all the information write to the next line so that i can
select another product, which will then also write to the next line, making a
type of shopping list.

Here is a way to do what you want

I am assuming that the dropdown list bax is called ComboBox1 and is on
Sheet1 and has a linked cell property of A1 and has a list fill range of
K1:K6.

I am also assuming that there is a range K1:M6 where K1 through K6 are
product names and L1 to L6 and M1 to M6 are certain info about each product.

Use the following code for ComboBox1's change event

Private Sub ComboBox1_Change()
With Sheet1
lLastRowWithData = lLastRowWithData + 1
.Cells(lLastRowWithData, 2).Value = _
WorksheetFunction.VLookup(.Range("a1"), .Range("K1:M6"), 2, True)
.Cells(lLastRowWithData, 3).Value = _
WorksheetFunction.VLookup(.Range("a1"), .Range("K1:M6"), 3, True)
End With
End Sub

Also place the following declaration in the Declaration section of the sheet1.
You will need a button or some user initiated method which will set the
lLastRowWithData variable back to zero when the user wants to start again.

Alok Joshi


"BLW" wrote:

i am using a dropdown list to show product names. when a product is selected
from the list, key information appears in the adjacent columns. what i need
to do now is make all the information write to the next line so that i can
select another product, which will then also write to the next line, making a
type of shopping list.

i think one of the problems that i am giong to have for this is that there
are several categories for this list and so there are several listboxes...the
second and third listboxes would be dependent on how many things were
selected from the first listbox.

"Alok" wrote:

Here is a way to do what you want

I am assuming that the dropdown list bax is called ComboBox1 and is on
Sheet1 and has a linked cell property of A1 and has a list fill range of
K1:K6.

I am also assuming that there is a range K1:M6 where K1 through K6 are
product names and L1 to L6 and M1 to M6 are certain info about each product.

Use the following code for ComboBox1's change event

Private Sub ComboBox1_Change()
With Sheet1
lLastRowWithData = lLastRowWithData + 1
.Cells(lLastRowWithData, 2).Value = _
WorksheetFunction.VLookup(.Range("a1"), .Range("K1:M6"), 2, True)
.Cells(lLastRowWithData, 3).Value = _
WorksheetFunction.VLookup(.Range("a1"), .Range("K1:M6"), 3, True)
End With
End Sub

Also place the following declaration in the Declaration section of the sheet1.
You will need a button or some user initiated method which will set the
lLastRowWithData variable back to zero when the user wants to start again.

Alok Joshi


"BLW" wrote:

i am using a dropdown list to show product names. when a product is selected
from the list, key information appears in the adjacent columns. what i need
to do now is make all the information write to the next line so that i can
select another product, which will then also write to the next line, making a
type of shopping list.

You have to explain the full scope of the problem and someone can probably
come up with a solution.

Alok Joshi

"BLW" wrote:

i think one of the problems that i am giong to have for this is that there
are several categories for this list and so there are several listboxes...the
second and third listboxes would be dependent on how many things were
selected from the first listbox.

"Alok" wrote:

Here is a way to do what you want

I am assuming that the dropdown list bax is called ComboBox1 and is on
Sheet1 and has a linked cell property of A1 and has a list fill range of
K1:K6.

I am also assuming that there is a range K1:M6 where K1 through K6 are
product names and L1 to L6 and M1 to M6 are certain info about each product.

Use the following code for ComboBox1's change event

Private Sub ComboBox1_Change()
With Sheet1
lLastRowWithData = lLastRowWithData + 1
.Cells(lLastRowWithData, 2).Value = _
WorksheetFunction.VLookup(.Range("a1"), .Range("K1:M6"), 2, True)
.Cells(lLastRowWithData, 3).Value = _
WorksheetFunction.VLookup(.Range("a1"), .Range("K1:M6"), 3, True)
End With
End Sub

Also place the following declaration in the Declaration section of the sheet1.
You will need a button or some user initiated method which will set the
lLastRowWithData variable back to zero when the user wants to start again.

Alok Joshi


"BLW" wrote:

i am using a dropdown list to show product names. when a product is selected
from the list, key information appears in the adjacent columns. what i need
to do now is make all the information write to the next line so that i can
select another product, which will then also write to the next line, making a
type of shopping list.