Determine with VBA which Area has been selected

I’m using a BeforeDoubleClick event to enter a check in the cell selected with the event below.

Please Login or Register  to view this content.

There are four columns of data with column A holding different area of data (four areas in this case). In each area there needs to be one check which the user can get with the event above.

Here’s what I can’t quite figure out. If the user wants to swap the check for another cell within each area, how can we delete the previous check and add the check to the new cell. Actually, I probably got the adding of the new check as it’s the target, but not sure how to remove the previous check from that one area.

So in the attached example, the first area is A2:A9. The check is currently in D6, but the user wants to change it to D8 so D6 needs to be cleared. The other three areas were not messed with so their checks should stay as is.

I’ve been researching the .Areas in VBA but can’t understand how to narrow the focus to the area affected.

Couldn't you clear all the checks in the area and then put a check in the appropriate cell?

Please Login or Register  to view this content.

Maybe this will work for you, or give you some ideas:

Please Login or Register  to view this content.

Thanks Trevor and Norie. I was indeed over complicating the matter. Both of these worked great.

Even though I've been on this forum for many years, you guys still amaze me at how well you know your way around this stuff.

Thanks again for your time...

You're welcome. Thanks for the rep.

Modified version to cater for initial setup:

Please Login or Register  to view this content.

Without this modification, the code will fail if there are no ticks present, that is, when you first start the selection process.

And, in that respect (if not others), Norie's solution is the better one

Thanks Trevor for the update.

I can see how you might say Norie's solution is better and indeed it's spot on, but your code gives me another angle and solution to ponder in an attempt to improve myself.

I'd rather have a couple of sound solutions than none at all

Again, you're welcome.

Trevor

My solution relies upon the named ranges Range1, Range2 etc existing, if they don't then it's kind of useless.

I was going try to write something a bit (lot really!) complicated until I found those named ranges - might have a go at that tomorrow.

Hi Norie,

You are spot on. The first way I attacked this was with 4 named ranges (Range1, Range2, Range3, and Range4).

I would be interested in seeing how you can do it without named ranges and by using the areas collection.

My solution doesn't use Named Ranges, it just uses the values in column A as "groups".

@Jeffrey: thanks for the feedback. Been playing around with this and, hopefully, an improved version:

Please Login or Register  to view this content.