Simple macro problem

[Lukey]

Im creating a system as part of a school project.
I have this drop down list.
\1
The product codes (C001 etc) are in written in a cells which are now hidden and i have used a VLOOKUP so the description from another sheet (ProductList) shows the description of each product when select.
Now i have this box on the same sheet as the drop down list.
\1
Basically i need a macro which i can attach to a button so that when a product is selected i can click "Add to Order" and it appears in the box and then i select another product and it does the same thing.
I hope i have made myself clear.
Grateful for any help.
Lukey

[Tom Ogilvy]

Perfectly clear except for any type of detail that might support providing
an answer.

Some assumptions

C002 is located in Cell B2 of Sheet1
Qty will be in cell C2 of Sheet1

the hidden rows of data for the Vlookup are in Sheet1, Cells( A101:F500).
In that range,
Product code is in column A
Description in column B
Price in column C

your order summary is on Sheet2 with the first product code for the first
item in cell F5 and you have allowed 10 rows for products


Assuming a CommandButton from the controls toolbox toolbar. After you add
the button (and still in design mode), double click on it or right click on
it and select view code - takes you to the code module for the sheet with
the declaration:

Private Sub CommandButton1_Click()
Dim rng as Range, res as Variant, cnt as Long
set rng = Worksheets("Sheet2").Range("F5")
cnt = Application.CountA(rng.resize(10,1))
if cnt > 9 then
msgbox "Bad design, Out of room"
exit sub
end if
res = Application.Match(Range("Sheet1!B2"),Range("Sheet1!A101:F500"),0)
if not iserror(res) then
rng.offset(cnt,0).Value = Range("Sheet1!A101")(res,1)
rng.offset(cnt,1).Value = Range("Sheet1!A101")(res,2)
rng.offset(cnt,2).value = Range("Sheet1!A101")(res,3)
rng.offset(cn,3).Value = Range("Sheet1!C2").Value
rng.offset(cnt,4).Value = rng.offset(cnt,2).Value * rng.offset(cnt,3).Value
Else
msgbox "Invalid Product Code"
end if
End Sub

--
Regards,
Tom Ogilvy



"Lukey" <[email protected]> wrote in
message news:[email protected]...
>
> Im creating a system as part of a school project.
> I have this drop down list.
> '[image: http://img35.imageshack.us/img35/8254/dropdownlist3zs.jpg]'
> (http://imageshack.us)
> The product codes (C001 etc) are in written in a cells which are now
> hidden and i have used a VLOOKUP so the description from another sheet
> (ProductList) shows the description of each product when select.
> Now i have this box on the same sheet as the drop down list.
> '[image: http://img5.imageshack.us/img5/4425/productbox5im.jpg]'
> (http://imageshack.us)
> Basically i need a macro which i can attach to a button so that when a
> product is selected i can click "Add to Order" and it appears in the
> box and then i select another product and it does the same thing.
> I hope i have made myself clear.
> Grateful for any help.
> Lukey
>
>
> --
> Lukey
> ------------------------------------------------------------------------
> Lukey's Profile:
http://www.excelforum.com/member.php...o&userid=30515
> View this thread: http://www.excelforum.com/showthread...hreadid=501687
>