Copy/paste data using checkbox-clear when unchckd & multiple selections paste in next cell

[Staceymcw]

Hi,
I've got checkbox in a worksheet called 'Checklist' and I am using the below code to copy the data in the cells next to the checkboxes when selected into another worksheet named 'Estimate'. The pasted cells are to start from B12. I need to be able to check multiple boxes and have them paste in the next available cells from B12, at the moment they just paste over the top of the current data in B12. I also would like the data cleared when the boxes are deselected, the only code I had figured out clears everything in the worksheet from B12, but I have info in various other columns and rows. I've attached my workbook, please help!!!

Module:

Please Login or Register  to view this content.

Checkbox macros (for each checkbox):

Please Login or Register  to view this content.

[humdingaling]

hmmm they way you are doing it is kind of complicated

i cleaned up all your check boxes and linked them up properly again with the help of this link
http://blog.contextures.com/archives...eck-yes-or-no/

after cleaning up your checkboxes i used an CSE (array) formula to collect a list of items tick checkbox ticked
the formula is

Please Login or Register  to view this content.

based on this output it fills in your front sheet (directly with just a single reference formula
in B12 = =Checklist!I7

i also created code to uncheck all checkboxes

Please Login or Register  to view this content.

ps i made the row height slightly higher at 18 (you had 15) just so the check box's look a bit better in the cell

[Staceymcw]

Hi humdingaling,

thank you so much for looking into this for me, the only thing is I am avoiding using a formula in the Estimate sheet. Sorry I should have mentioned this to begin with!! Im building this worksheet for some tradies to use who have very basic understanding of excel. The issue with putting formulas in is that they always clear the cell in 'Estimate', therefore clearing the formula. Hence im trying to do it through a macro so they cant just clear it away. Would you have any ideas on how I could fix my macro to do what I need without using a formula in 'estimate'?

Thanks for your help!

[humdingaling]

in that case
put this code in worksheet(estimate)

Please Login or Register  to view this content.

it will automatically copy helper section in column I and paste estimate

[humdingaling]

alternatively you can put a button there called "update" and make it do it instead of relying on calculate trigger

also i suggest hiding column I and column D
and putting in some application.screenupdating code in there so it doesn't "Flicker"

i have updated workbook with these suggestions

[Staceymcw]

absolutely amazing!! thanks you so much humdingaling :D

[humdingaling]

not a problem

good luck with it