Link to home
Avatar of Bright01
Bright01Flag for United States of America

asked on

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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

You/we used to do that. Go back to one of our old questions and you can find the code.
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Bright01

ASKER

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.
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.
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.
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
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"
Try Target.Forecolor = vbGreen 'or vbYellow or vbRed