Enhancement to Check Box Macro

EE Pros,

I have a great macro that provides me a check mark in a selected box.  How do I add the ability  in the Macro, to move from a check mark, to a  Question mark, to an X?  In other words, when you click on a blank black square, you should get a check mark, then double click again and you get a  ?,  a third time, and you get an X.  Click final  time and it recycles to a blank box.

Attached is the workbook/worksheet.
Priority-Check.xlsm
Bright01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
You/we used to do that. Go back to one of our old questions and you can find the code.
Martin LissOlder than dirtCommented:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

' Only cells in column E with something in them
If Not Intersect(Target, Range("E5:E" & Range("E1048576").End(xlUp).Row)) Is Nothing Then
    Application.ScreenUpdating = False
    With ActiveCell
        ' Only cells with black fill
        If Target.Interior.Color = 0 Then
            Select Case .Value
                Case "P"
                    .Font.Name = "Helvitica"
                    .Value = "?"
                Case "?"
                    .Font.Name = "Helvitica"
                    .Value = "X"
                Case "X"
                    .Value = ""
                Case Else
                    .Font.Name = "Wingdings 2"
                    .Value = "P"
                    .Offset(0, 4).FormulaR1C1 = ""            '"=Value"    'Value is not a defined name
            End Select
        End If
    End With
    'These two lines below make it a realtime update vs. using a button and the sub "sumup"
    Cancel = True
'ActiveSheet.Protect Password:="pass"
    Application.ScreenUpdating = True
    
'    sumup
End If

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Martin,

You are correct.  My challenge was that I thought it referenced something in one of the Macros so I was trying to get it to be standalone for a single Worksheet.  I used the code and it works perfectly.

Thanks for saving us both time on this.

Have a great weekend!

B.
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Bright01Author Commented:
Martin,

You're going to kill me for not being clear enough here.

I stumbled across one problem.  You did alter the code to use Cases.  Here's the issue; I have a Name Range called "Cell" that is a Workbook referenced Range name.  When you modified the code to include

With ActiveCell
        ' Only cells with black fill
        If Target.Interior.Color = 0 Then
            Select Case .Value

It messes up another Worksheet and the Worksheet we were working on doesn't work.   When I go back and compare them, there is something your code does to render the Range Name "Cell" to be linked only to the Worksheet and the value is in Column O for some reason.  Is there a way that we can reference the Black Cells in E without screwing up the other Range Name?  (e.g. call it something different?)

B.
Bright01Author Commented:
Also, if you wouldn't mind, can you tell me the single line to add "color" and "bold" to each of the Characters?  Conditional formatting won't work.

B.
Martin LissOlder than dirtCommented:
If you compare the named ranges in the workbook you uploaded with the one that I uploaded you'll see that the named range cell exists twice in both so my code modification had nothing to do with the problem you are experiencing.

I don't understand what "can you tell me the single line to add "color" and "bold" to each of the Characters?" means
Bright01Author Commented:
OK.... perhaps I'm getting a little smarter about this.  I took your sheet (which works flawlessly), deleted mine, and copied yours into my production WB.  AND IT NOW WORKS!  I don't know what I did wrong in my version but I spent about 2 hours trying to trouble shoot it.  "Thank you" once again.

So as for color, I was trying to make the check - green, the question mark  - yellow and the X - red using conditional formatting.  It would only do two of the colors although I built in all 3; so the X was also yellow.  So I thought I could add a statement such as  ".color =" in each of these 3 Cases just as you have .Font and .Value.  That's the essence of the color question.

If Target.Interior.Color = 0 Then
            Select Case .Value
                Case "P"
                    .Font.Name = "Helvitica"
                    .Value = "?"
                Case "?"
                    .Font.Name = "Helvitica"
                    .Value = "X"
                Case "X"
                    .Value = ""
                Case Else
                    .Font.Name = "Wingdings 2"
                    .Value = "P"
Martin LissOlder than dirtCommented:
Try Target.Forecolor = vbGreen 'or vbYellow or vbRed
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.