Add Checkmarks To Cells If Conditions Apply

How would I go about doing this? I need to run a code whenever a Cell in J9:V75 is double-clicked or receives a value:

*If the corresponding Cell in the ‘I9:I75’ column contains the word ‘Visual’ or ‘CMM’ or the corresponding Cell in the ‘B’ column contains the word ‘SECONDARY’, then the J9:V75 Cell needs to receive a checkmark – the letter ‘P’ in font Wingdings 2, size 16.
-For example, if I9 contains the value ‘CMM’, then upon double-clicking J9 it should display the checkmark.

*If the corresponding Cell in the ‘I’ column contains the word ‘Surface Tester’ and its corresponding ‘B’ Cell contains a number, then the J9:V75 Cell needs to receive a ‘< XX’ where ‘XX’ is the number from the corresponding ‘B’ Cell.
-For example, if B9 contains the value ‘RA 32’ and I9 contains the word “Surface Tester’, then upon double-clicking J9 it should display ‘< 32’.

This also needs to apply for the other two identical pages beneath the first. Please see the attached file for examples and clarity, and thanks so much in advance!

Why do you have merged cells? that only complicates things. If you want the cells set to double height, format them to double height. Also the form layout works against a solution. "Stacking" one form on top of another makes identifying target ranges more difficult.

Thanks for your reply! And yes, I know very well that merged cells are a pain, but unfortunately I am forced to used them, since there are often cases where I need to have multiple smaller cells in the space of the one large merged one. The stacked form layout is also a necessity in my case, sadly. Perhaps I should take another look at my options. Thanks again!

In spite of the "challenging" (looking for a euphemistic way to put it) layout, I'm going to take a shot at this. It will be very fragile code in that if the format changes even slightly, there is a good probability it will break.

I'm glad you informed me about the possibility of putting additional data in the lower cell. I will *not* be looking at this data. I will be depending on the form ranges not changing and that the data of interest is in the odd numbered rows.

Thank you, I really appreciate that. There's some code currently in there which works really well for detecting standalone letter 'L's in the Drawing Requirement column, perhaps that will be slightly helpful?

I think my work here is done. One feature I wanted to add was to allow you to delete an entry. Without code to do that, the checkmarks or <## would be left behind. I had a bit of a time testing to see if the cell was blank until I realized that the cells were merged, so I had to do a "two step" and parse out the part of the cell to test.

Thanks so much for your hard work! I did some testing and here is what I found:

1. When the corresponding Cell in the I9:I75 column contains the word ‘Visual’ or ‘CMM’ or the corresponding Cell in the 'B' column contains the word ‘SECONDARY’, then the entire row of cells in the J9:V75 area receives checkmarks. However, only the cell (or cells) within that row that is double-clicked or has received data should display the checkmark. I apologize if this wasn't clear enough in my first post.

For example, if I9 contains the value ‘CMM’, then upon double-clicking J9, only J9 should display the checkmark.

2. If the corresponding Cell in the ‘I' column contains the word ‘Surface Tester’ and its corresponding ‘B’ Cell contains a number, only then should the J9:V75 Cell receive a ‘< XX’ where ‘XX’ is the number from the corresponding ‘B’ Cell, once it's double-clicked or receives data. At the moment, however, entering any number into the 'B' column populates the entire J9:V75 row.

For example, if B9 contains the value ‘RA 32’ AND I9 contains the word “Surface Tester’, THEN upon entering the word 'hi' in J9 and hitting Enter, only J9 should display ‘< 32’.

The clearing of the cells is really nice, as long as those two points can be applied. Are they doable? I'd love to hear your thoughts. Thank you so much again for your help with this!

OK, I was mistaken. If I hear you correctly, the code should not fire on entries into B and I, but on a double click in J, M, P, S, or V. When that happens, the code checks B and I to see what (if anything) it should put in the cell. Is this correct?

If so, I think I have some of the pieces already in place; I will have to shuffle them around.

Yes! That's absolutely correct. The double-click is what triggers it, and it would be most helpful if any data entered in J, M, P, S, or V also triggers it. So two triggers, if that makes sense and if that works.

Well, here is the version with the double-click trigger.

I'm not sure what you want to do if you enter in data in J, M, P, S, and V. Do you mean that you want to either double click or enter any value to fire the code?

That's really close! And yes, I do mean that either double clicking or entering any value should trigger the code, would that work?

Also, is there a way to have the cell exit the 'data editing mode' (with the flashing cursor) after the double click if there is an automatic entry?

Thanks again!

I have to admit that I made that assumption and did work along that line. I learned that you can have two events on the same page. However I ran into a problem in that if you change the cell value by double clicking on it, the change event picks this up and fires off after the double click event.

For this particular case, it's merely a waste of time. You want to do the same thing no matter what causes the event. However, if you wanted double click to do one thing and cell value change to do another, then there's an issue.

However, your comment about having the cell exit the "data editing mode" gave me an idea for a solution. I can assign some sort of attribute to the cell when either event fires and check for it if the other event tries to change it. On the top of my head is to put a date stamp in the comments for the cell that will tell you when the cell was changed.

Having said that, I don't know what you mean by flashing cursor. I am assuming by automatic entry you mean entry by firing off one of the events.

Would something like this be helpful? This is a checkbox code I've used previously for cells that were always set to be checkboxes. Upon double clicking a cell (in this case any cell from D17 to D22) a checkmark is entered and the cell is just selected again (it doesn't even go into the 'data editing mode with the flashing cursor' so to speak). Double clicking the cell again clears the checkmark.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("D17:D22")) Is Nothing Then Exit Sub
    Target.Font.Name = "wingdings 2"
    If Target.Value <> "P" Then
        Target.Value = "P"
        Cancel = True
        Exit Sub
    End If
    If Target.Value = "P" Then
        Target.ClearContents
        Cancel = True
        Exit Sub
    End If
End Sub

Was my last post useful at all? I'm still struggling with putting this all together. I think I can let the < XX thing go to make this simpler, but the checkmark thing would be really nice to figure out.

The merged cells are being a real PITA.

I eliminated the change event. You can only change cells by double clicking on them. The issue is that when you double click, you add something to the cell. This is detected by the change event and it fires as well. You can "trap" the fact that the change was made by a double click and end sub immediately with the change event, but it gets messy in that there are a lot of things you can't do with merged cells.

Merged cells are acceptable as column headers (even there they give problems), but are absolutely a bad idea for storing data except in some extreme cases where they are merely a bad idea.

Awesome, that is really close now! I've been testing it out and I have one final problem - when I double-click a cell that already contains a value in order to edit part of it, the entire data vanishes. Is there a way to prevent that? Thank you again!

I'm not seeing that issue. If I double click on an existing value, nothing happens unless I change other data.

For example I had "Number 32" in Column B and "Visual" in Column I. According to the rules, that translates to a check box. I changed Column I to "Surface Tester" and went back and double clicked the box and it put in "<32."

Explain to me exactly what you are doing so I can try to duplicate the error.

Ah yes, my bad, I didn't explain properly.

Say I have the word 'Caliper' in a cell in Column I and I enter 12346 into a corresponding cell. When I realize afterwards that I should have entered 12345 instead and I double-click the cell to change the 5 to a 6, the entire number disappears.

Would we be able to prevent the clearing-the-cell part of the double-click from occurring if there is no proper match?

You want to type something in manually in the cells that should be double clicked? I don't see why (a) you can't do that, and (b) just go up to the formula bar to edit it. You do not have to double click it to make it editable. If you want to edit it in cell, then you need to double click it, but you can always use the formula bar.

True, but in this worksheet's usage, it is more practical to double-click the cell to edit it than to use the formula bar. Would there be no way to only run the code if there is a match in Column I, and otherwise just let the user enter/edit data as usual?

Comment out the indicated line in the code.

Else
    ' MyRange.Value = "" <------ this is what clears the cell. The message box will still come up, but you can edit the cell after you check OK.
    MsgBox "No action taken", vbOKOnly, "Conditions not met"
End If

End Sub

Oh my goodness, that is so close, is there no way to turn off that message box? It's a bit of a pain. :P

Comment it out too.

I ended up going with this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim TargCol As String           'Target column used to determine what rules to apply
Dim MyNum As Long               'Number embedded in a string (-1 means no number)
Dim TRow As Long                'Target Row
'Dim RealTarget() As String     'Array to hold pieces of the merged cell
Dim RangeString As String       'Long string containing the non-contiguous ranges

RangeString = "J9:J76,J89:J156,J169:J236,"
RangeString = RangeString & "M9:M76,M89:M156,M169:M236,"
RangeString = RangeString & "P9:P76,P89:P156,P169:P236,"
RangeString = RangeString & "S9:S76,S89:S156,S169:S236,"
RangeString = RangeString & "V9:V76,V89:V156,V169:V236"

'Limit the event to sections withing the three forms
If Intersect(Target, Range(RangeString)) Is Nothing Then Exit Sub

'Limit the event to only even numbered rows
If Target.Row Mod 2 = 0 Then Exit Sub
Application.EnableEvents = False

TRow = Target.Row
MyNum = ParseNum(Cells(TRow, "B"))

'Check values in Column B and I
If InStr(UCase(Cells(TRow, "B")), "SECONDARY") > 0 Or _
    InStr(UCase(Cells(TRow, "I")), "VISUAL") > 0 Or _
    InStr(UCase(Cells(TRow, "I")), "CMM") > 0 Then
        Target.Value = "P"
        Target.Font.Name = "Wingdings 2"
        Target.Font.Size = "16"
        Cancel = True
ElseIf MyNum > -1 And UCase(Cells(TRow, "I")) = "SURFACE TESTER" Then
    Target.Value = "< " & MyNum
    Target.Font.Name = "Century Gothic"
    Target.Font.Size = "10"
    Cancel = True
Else
End If
Application.EnableEvents = True
End Sub

And it seems to work just great now! Thanks so much, you were a huge help!