Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple macro problem
Im creating a system as part of a school project. I have this drop down list. '[image: http://img35.imageshack.us/img35/825...wnlist3zs.jpg] (http://imageshack.us) The product codes (C001 etc) are in written in a cells which are no hidden and i have used a VLOOKUP so the description from another shee (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 product is selected i can click "Add to Order" and it appears in th box and then i select another product and it does the same thing. I hope i have made myself clear. Grateful for any help. Luke -- Luke ----------------------------------------------------------------------- Lukey's Profile: http://www.excelforum.com/member.php...fo&userid=3051 View this thread: http://www.excelforum.com/showthread.php?threadid=50168 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple macro problem
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Really simple macro problem - I bet! | New Users to Excel | |||
Simple problem, simple formula, no FUNCTION ! | Excel Worksheet Functions | |||
Simple IF problem | Excel Discussion (Misc queries) | |||
Help simple problem | Excel Programming | |||
A simple problem... | Excel Programming |