Changing cell properties with double click, then revert with another double click

Good evening,

I have wrestled with this for a week now and can't get anywhere. I have a sheet (attached below) that I am trying to write in VBA the ability to double click a cell and turn it a color, then double click again and remove the color (reverts to no fill). I want to be able to count the number of green cells, red cells, etc. Additionally, I would like to be able to restrict the user to only selecting one choice from each row (ex. row 12 can only double click "C" - turning it green - and if they double click "I" afterwards, an error message pops up and they are unable to select the second cell).

Can anyone help me get on the right track? I initially went with checkboxes in each individual cell, linked to hidden cells so that I could count the "True" and "False" counts. This checkbox method is unwieldy, and there has to be an easier way. In the sheet attached below, the cells in question are I12:L21.

Thanks so much in advance for any help!

Matt

SampleBook.xlsx

I am a little more novice I think haha. I tried to record a macro where I simply doubleclicked a cell to see how it recorded it, but it only noted that I selected the cell...it didn't necessarily record the double click. I'm confused about the target parameter you mention. I'm assuming this parameter is the color (or non-color) of the cell, but how do I incorporate that into the code?

Thanks for your response!

Thanks guys.I have made a lot of progress tonight with your help.

Marvin, I borrowed some of your code to set up the error message (I know what you mean about GoTo not being the best option, but I would like to get it working before I start streamlining). In the attached document, you can see where I am having trouble. I have 4 separate programs set up side by side (this side by side setup is non-negotiable). For example, on line 12 I can have one option (I,F,P,or C) selected for each of the four programs. Each of the programs is treated separately. However, if there are multiple cells doubleclicked per program, I would like to show the error message.

If Range("I12:L12").Interior.Color <> vbWhite Or _
Range("I12:L12").Interior.Color <> vbWhite Or _
Range("I12:L12").Interior.Color <> vbWhite Or _
Range("I12:L12").Interior.Color <> vbWhite Then
MsgBox "You can't have more than one entry per line.", vbOKOnly + vbCritical, "Error"

GoTo Ender
End If

As you can see in the code, I named the range that should have been affected ("I12:L12"). The error message works fine for the first program (Found from I12:L21), but when I go to doubleclick any options in the other programs, I get the error message. Why is this happening? If I named the range that encompasses the first program and nothing else, why does the error message show up when I move to other parts of the sheet?

Thanks again for all of your help! You guys are the best

Thanks Marvin! I will read through those and come back if I still am having problems.

So everything went well until I coded the whole sheet and i got a compile error because "Procedure Too Large". I had to do the intersect for each row of 4 cells that I am trying to only allow 1 entry for. So basically 48 of the below back to back. (I have the "Ender" at the bottom of all of the code). Everything works exactly as it should, it is just too much for VBA to handle.

To reiterate, I have four ranges:
-I12:L21
-Q12:T21
-Y12:AB21
-AG12:AJ21

But each line (i.e. I12:L12, or Q12:T12) can only have one selection. I had trouble using the intersect when I name the large range (ex - I12:L21) so I went with using a separate intersect for each 4 cell row (ex - I12:L12) as seen below.

I am sure that I made it much more complicated than it should be, but I am new to VBA and it was working like I wanted, it just became too large. Any advice on trimming it or making it more efficient so that it will fit?

Please Login or Register  to view this content.

I'm not sure how that could happen. I have 32 bit and it was all written in my program.

Do you have any tips to use less code to achieve my goal? The code runs fine up until about 42 iterations of the code I pasted above, I'm guessing the last 6 iterations push it over some kind of limit. Shortening the code should fix it, in that case.

Are there parts of my code that I could transfer to public sub and then call when I need them? So basically 48 different public subs, one for each 4 cell row that I am dealing with? I tried to do it by copying everything from the above code from (If Not Intersect...) down to the last End If, moving it to a new sub, naming it something like change1, and then calling change1 when it is required. I then get "Argument Not Optional" error.

I know I'm in over my head and taking time to learn everything from square one would be the best, but I am in a severe time crunch and am really struggling. Thanks for any help in advance to shorten the code and avoid the "Procedure Too Large" error.

I do have a cancel = True at the very bottom of the code. Basically I have 48 versions of what I posted above, and underneath all of those I have

Ender:

Cancel = True

End Sub


I guess another possibility would be to have multiple BeforeDoubleClick events? I tried this also but when I try to copy the block of code and paste it below, using all of the same logic but simply changing the title of the sub to "BeforeDoubleClick1" it doesn't seem to work.