Cannot figure out how to create macro to work with button for needed functionality

[chairokeeman]

I am working on a project for work. I have two work sheets selection page and pricing page.

My goal is to have my customer select one of the options for each of seven different categories on the selection page and then the row will be moved to the pricing page so that he can easily see what the different options will add up to in price.

I don't know how to create a macro that will allow me to do that. In a nutshell, I need the macro to copy cells
b4 - Products
c4 - Option
d4 - Price
e4 - Quantity
f4 - Extended Price
from selection page to the same cells on pricing page.

Thanks for your help in advance.

Luke

[Wanting_To_Learn]

Luke,

I think I may need some more information. Would a user form with option buttons fit best for this problem? And what kind/how many choices does the user have?

Stayci

[tigeravatar]

Luke,


Something like this?

Please Login or Register  to view this content.

~tigeravatar

[chairokeeman]

I am attaching the sheet so you can see what I am talking about. I did take out anything identifiable.

So what I am attempting do is add a button like line 3 sheet 1 for all of the products. When my customer clicks that button or one in the "lev" section the info from that line slides the "pricing page" sheet to line 7 and populates those fields.

The same goes for the products under the r, dc, shelter, seal, and bump sections on the selection page to the corresponding lines on the pricing page sheet.

I hope I am explaining this clearly enough.

Thanks,

Luke

[tigeravatar]

Luke,

I modified your sample sheet to bind a macro to the buttons which (I believe) achieves the desired effect. Let me know if you're looking for something different.

Hope this helps,
~tigeravatar

[chairokeeman]

That is fantastic. Thank you so much for your help on this. One other question for you though.

If I need to add a three columns for taxes, installation and Line item total to the right of extended price what would I change in the macro to in include the extra three columns.

Thanks,

Luke

[chairokeeman]

Tigeravatar,

My business partners are having a hard time opening the document. They are getting a runtime error 438. Is there any solution for that? I know they have the compatibility pack but its still not working.

Thoughts.

Thanks for your help.

[tigeravatar]

Luke,

In response to your first question:

If I need to add a three columns for taxes, installation and Line item total to the right of extended price what would I change in the macro to in include the extra three columns.

In the macro, you will see a lot of range "B" to "F". The F is the item you would need to change. Just change it to the column letter you'll be expanding out to (three more rows would bring it to column "i").


The second question:

My business partners are having a hard time opening the document. They are getting a runtime error 438. Is there any solution for that? I know they have the compatibility pack but its still not working.

I'm not sure what it causing this. I would make sure they are opening it in Excel and not a different program, like Symphony or OpenOffice, as they might not be able to interpret the macro correctly, which could cause the error. Also, you could try saving the file as .xls format instead of .xlsm (macro should still be able to run in 2003) and see if that helps.

~tigeravatar