Copy double-clicked cell to next empty row in different sheet with each double-click

Hi

That code looks fine to me based on what you described. Can you please upload a copy of your workbook, including somewhere in the workbook showing me the result that you expect to see after this macro is run and what it is doing now. This will allow me to step through your code in the same context as you, just to make sure that I am getting the correct solution for you.

Thanks

Sure!

Attached is the workbook. Let me know if I can help clarify anything.

Product Catalogue - Test.xlsm

Hi,

Here is a working version of what I think you want. Please take a look at the changes I made to the VBA and see if it is what you are asking for.

Notes:
1. The vendor name is now in a named range called "VendorName" - this is important as it is being used in VBA
2. The cell that says "Shipping" is now in a named range called "Shipping" - this is important as it is being used in VBA
3. The formula that calculates the total value of the PO has changed now - this is important as the sum range is variable if you are constantly adding rows to the order form

Let me know if this is what you are after.

Thanks! That is exactly what I was looking for.

I am also curious as to how I could press a button (called "Save PO") on the Purchase Order sheet and have it take all the products in the Purchase Order and copy them to another sheet ("Orders Placed")

I understand most of the code needed to make this happen, but I don't understand how to copy the products from the PO given that the size of the sheet is changing depending on how many items were added.

Thanks again.

Hi again,

The following code can be inserted on a button click event to copy the product rows to a worksheet called "Orders Placed":

Please Login or Register  to view this content.

It is important to note that I have only given you enough code to copy these lines, as this was what you weren't sure about. I've left the formatting and other data copying up to you. This code also assumed that the required worksheet already exists, so you may want to perform a check first and if it doesn't exist, then have the VBA code create it for you, and then copy the headings across.

Once your purchase order has been saved, you could then call the following subroutine to reset the purchase order back to its original state:

Please Login or Register  to view this content.

Hope this helps

Thanks again! This is all very helpful and informative!

I have one last issue: the sum that calculates the total doesn't seem to work if more than 4 products are added...

What formula do you have in the cell?

I changed it to the following so that it accommodates the insertion of rows above:

Formula:

Please Login or Register  to view this content.

Let me know

I plugged that in and couldn't get it to work.

As a reminder I want to set the value of column K equal to the value of column A x column J. All the values in Column K are summed and added to shipping and tax to give the value of Total.

Please Login or Register  to view this content.

What do you think is wrong? Thanks!

Ah sorry about the misunderstanding, I thought the "TOTAL" cell was giving you the wrong value, I didn't realise that you were referring to the "Calculate Total" button.

In that case, change the subroutine to reset the purchase order (if you decided to use it) to

Please Login or Register  to view this content.

and change the Calculate Total subroutine to

Please Login or Register  to view this content.

Hope this works now

Ah great. Thanks! It works perfectly now.

I have another question:
I am trying to create radio buttons that control whether the double click event is capable of occurring. I've created the radio button code:

Please Login or Register  to view this content.

And I have nested an If statement in the beforedoubleclick event sub:

Please Login or Register  to view this content.

But now the feature of adding a product by double-clicking doesn't work. I can't figure this out...

Hi, that's good to hear.

Could you please upload an updated version of your workbook with the radio buttons for me to have a look?

Thanks