Enhancement to Check Box Macro

Bright01
Bright01 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You/we used to do that. Go back to one of our old questions and you can find the code.
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
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

Author

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 ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Author

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.

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try Target.Forecolor = vbGreen 'or vbYellow or vbRed

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial