Solved: Replace formula with vb event

[Sir Babydum GBE]

In the attached example, I have a "control panel" that contains some named ranges: "Areas", "Start Date", "End Date", (they?re all actually just single cells ? named for making it easier to read formulae).

You?ll notice that changing the number in cell C3 ("Areas") increases the number of areas accordingly. I?ve done this with formulae and conditional formatting. The problem with this method is that ? whilst it is a relatively sexy method to get the desired result ? it is a tad long-winded and takes up a fair amount of space. But it has a bigger, more fundamental flaw:

If I have started populating this sheet with information, then I need that information to move relative to the new position of the "areas". So I think I need to scrap the formulae. I think I need a worksheet_change event that physically adds rows (populated with "area", "day", and "date") when the user increases the number of areas in the control panel ? but the problem there is that if the rooms are decreased ? then a warning message informs the user that if he continues ? he will delete all records from the rows that are about to be deleted.

Any help greatly appreciated

[Bob Phillips]

Shame, it is nice

[Sir Babydum GBE]

What can I say?

Thank you.

Your code is so short - If I'd attempted that my code would have been 20,000 words long and wouldn't have worked. I did try to add one thing to your code (forgive me): Application.screenupdating=false - but it didn't make any difference. But I'm not asking you to look at that because any users of this sheet had jolly well better respect it for what it is!!

[Bob Phillips]

What can I say?

Thank you.

Your code is so short - If I'd attempted that my code would have been 20,000 words long and wouldn't have worked. I did try to add one thing to your code (forgive me): Application.screenupdating=false - but it didn't make any difference. But I'm not asking you to look at that because any users of this sheet had jolly well better respect it for what it is!!

My pleasure sir.

I also tried screenupdating but it made no difference.

[Bob Phillips]

Th error of my ways has been pointed out to me

[mvidas]

Hello,

I had started on this earlier, but got pulled into something else and didn't finish it until just now. I see xld has already given you a solution, but I figured I'd post this anyways, as it updates more than just the areas. If you change anything in the control panel, this will update. However, instead of doing it on cell change there is a button that needs to be pressed. I'd still go with xld's, as I'm sure it does exactly as you need, but I figured I'd post this nonetheless.

Matt