Double Click Event to change and limit other cells

**Bonnister** · 02-01-2013, 11:33 AM

DoubleClickEvent.xlsxHello all

I've attached a file to help demonstrate what i'm tryng to achieve, and as usual any help is gratefully received and appreciated.

In the attached file are 10 options available to five different users. So far I have some VBA code set up so that any cell double clicked in the range C3 to G12 will toglle between 'Yes' and 'No' values. All well and good so far...

What I want to be able to do now though is the following - if Option1 is selected 'Yes' for any given user (so in this example let's use User2, in column D), that this change will cause two things to happen:

1) it will change all other options for that particular user (ie: from Option2 to Option10) to a 'No' value.
2) it will then stop the double click event working if you then try to change one of those options to a 'Yes' while option 1 is still set at 'Yes'.

For reference this is the VBA I'm using so far just to deal with the Yes/No toggle:

Please Login or Register  to view this content.

Can anyone help please??

Thanks
Kenny

**6StringJazzer** · 02-01-2013, 02:54 PM

First, the file you attached is not macro-enabled. It would be helpful when you have a file with a macro to post the actual file. Also the data in the attached file does not match your description; it only has two columns of responses.

Here is code that will work for what you described. I don't know where you got Range("l30:cd48") but I changed it to the range you described.

In this case, if the user double-clicks when Option 1 is Yes, then the default action will occur, which is opening the cell for editing. If you do not want to do that, then don't change Cancel to False. Leaving it True will simply result in nothing at all happening after the double-click.

Please Login or Register  to view this content.

**Bonnister** · 02-04-2013, 05:03 AM

Hi 6StringJazzer

Apologies for my original post, I was in a rush to get it submitted while at work and since reading it back I realise a lot was wrong about it! However I've just tested your code however and it works perfectly, thank you very much indeed

Kenny