Groups - automatic filling of cells

I don't think grouping is the way to go here - although it might make things visually simpler.

I'd create a table for each individual kit, and do some processing using the Worksheet_Change() event to manipulate the entries. The benefit of using a table is that rows and/or columns can be added or deleted without it being problematic for the VBA routines. Delete the extra items I've inserted, or add extra ones to try it out!

I've attached a workbook with a bit of code I've come up with to demonstrate a possible approach. Have a look at the VBA - I've commented it a fair bit so you can see what it's doing. It looks more complicated because I've tried to make it fairly generic so it can apply to all tables on the worksheet, otherwise you'd have to edit the various ranges every time a table changed.

Tim


EDIT: tweaked so 1st column of the tables are disregarded - otherwise it would be impossible to edit kit items!

Hope so. If you consider it done, please remember to mark the thread "SOLVED" using the thread tools at the top of the page.

You can also show your appreciation for those that have helped by clicking the * at the left of each post if you're so inclined.

Tim

I'm not quite sure what the purpose of the group "total" is. Am I right in thinking that it's to distinguish between group items, and optional ones?

Anyway, you've got conflicting interests here... in the _Change() routine, you haven't switched off Application.EneableEvents. This has the effect of changing the names successfully i.e. if you put "Tim" in E14 it populates the cells above, as intended, by triggering a new Worksheet_Change() instance for each cell.

Leaving Application.EnableEvents = true, however, will cause a separate iteration of the Worksheet_Change() event, and it can cause a infinite loops. I think that's your problem with the line

Please Login or Register  to view this content.

Each change triggers another Worksheet_Change() event which results in the error you have seen. (I've not been able to replicate that, specifically, but there seem to be several anomalies causing problems.)

Additionally, although I've not spent a lot of time analysing it, you'll notice that sometimes you get "Runtime Error 1004" - not enough memory. The reason for that is because this

Please Login or Register  to view this content.

never completes. If you press "Debug" on the error message and hover the cursor over the word "row" you'll see it's looped beyond 1.04 million times.

One final observation (before I go nuts)... your line

Please Login or Register  to view this content.

only defines column as Integer... row is a Variant as the type isn't defined. This may cause a problem further down when you're evaluating cell values. If the cell value contains a number, but you're looking for "1", you're actually comparing a number with a string. On reflection, I wonder whether that's the issue with this looping forever:

Please Login or Register  to view this content.

I've re-attached a copy of my original reply, slightly modified with the extra "totals" column, and with optional items simply placed below the table for each kit. If the "Totals" column doesn't do what you want it to, you can simply exclude it from the other events for the table body by uncommenting the additional line I've put there; change it to an If ... Then statement to make it do what you want if you don't just want to exit at that point.

You could, also, trigger additional events to automate things for the optional items if you want to.

Tim

No worries... There are often reasons we do things the way we do that aren't obvious from cut-down sample files. You're right... It won't loop forever as long as it finds a match at some point.

Tim