Macro for copying combobox entries to another sheet

We would need to

a) see the code

b) know whether you're using ActiveX of Forms controls

On an aside, you could potentially use Data Validation rather than Combo Boxes to regulate/normalise data entry

Before going too far down the route of ComboBox - I would suggest (tentatively) have a look at using Dynamic Named Ranges re: source for Data Validation lists - see link in my sig. for a guide. Not advocating one way or the other but you find DNR's a useful approach regardless.

We should probably also see how you're populating the ComboBox (multi bound columns ?) and understand the linked cell settings that have been applied... perhaps a watered down version of the file itself ?

I will be off line most of today but others will help.

Can you elaborate in terms of what you meant by:

...reproduce the dynamic named range

If you mean you want to use the same dimensions but for a different range you can in theory "Offset" from the original but in reality we need more info.

I wonder perhaps if we're best off working backwards... I've had a look and through the file and though I have no doubt you could get this to work it will I fear prove a complex approach long term...
The key to any spreadsheet is (in my experience) how effectively you store your source data and for that I think it's best to think in terms of databases...
For ex. if you take your Recipe Main sheet you should really think about each Ingredient as a separate row with an additional column to specify which "master" recipe the ingredient belongs to... ie more rows / fewer columns (1 column for qty, 1 column for Unit)
(and possible one further validation list to store a master list of recipes (presently you use column B on Recipe Main))


Having spent a few minutes looking at this it appears to me the workflow of the file is:

a) As and when required...

User decides to add a new recipe
To do so they complete Recipe Enter sheet and run macro to transfer data to new row on Recipe Main sheet
(dropdowns are populated via your DNRs to help normalise data)

b) On Dinners sheet user picks from options the various recipes available

c) On Shopping List is to be populated based on ingredients required for each recipe selected on Dinners sheet

Is that correct ?

OK, give me some time and I will put something together which I think is likely to benefit you long-term...

you can get totals of items using offset sum product against your grand list of items

well again it doesnt have to be on that sheet it could be on the ingredients tab or just mirrored like ive done =ingredients! b2
but still reference the existing shopping list then you could simply have a button saying generate shopping list" linked to a macro that auto filtered on non 0 values and copied it to a new nice sheet that had just that info for shopping list
like this one (macro 1) and macro 4 to reset on "real shoping list" sheet, its part recorded part adapted alot of the" selection." could be replaced but it works fine

I've taken the liberty of attaching an approach that I personally feel is likely to prove beneficial long-term - I'm not advocating this over anyone elses and as always it stems from personal preferences... as such you are obliviously under no obligation to adopt etc... my ego will remain intact should you prefer not to

Basic premise of the file is as before only now there is a Pivot Table to display current Shopping Requirements based on the current Dinner selections... as and when these selections are altered so the PT will update automatically (updated by combination of formulae on Recipe_Detail sheet and VBA Change event on Dinners sheet)

I revised the new Recipe form a little (column G can be hidden - it's merely a basic error check) ... I re-wrote the VBA such that the header info (C2:C6) is written to the master Recipe List whilst the Ingredients info is written to the Recipe Detail sheet.

Basic aim of all of the above is to try to keep the file looking as clean as possible whilst storing data in a database like manner. I hope it helps.

DrPips, I was just looking at this again and noted an error in one of the error checks... for ref. updated version attached.