Check Mark Box - Windings

[RJ1969]

Hi
I have this code and it is set windings 2. I want to have a "X" mark instead of a check mark.

Please tell me what I need to change. thank you.

If Target.Address(0, 0) = "O16" Then
Target.Font.Name = "WingDings 2"
Target.Font.Bold = True
Target.Font.ColorIndex = 3
If Target.Value = "" Then
Target.Value = "P"
'Range("L16").Value = ""
Target.Offset(0, 1).Select
Else
Target.Value = ""
'Range("L16").Value = ""
Target.Offset(0, 1).Select
End If
End If

[RJ1969]

Also,
I tried to merge O16-P17 but the code stop working.

Here is the revised code.

If Target.Address(0, 0) = "O16:P17" Then
Target.Font.Name = "WingDings 2"
Target.Font.Bold = True
Target.Font.ColorIndex = 3
If Target.Value = "" Then
Target.Value = "P"
Target.Offset(0, 1).Select
Else
Target.Value = ""
Target.Offset(0, 1).Select
End If
End If

[RJ1969]

I am trying to figure it out what is on the code. Target.Value = "P" is a checkmark so what is letter for X?

[protonLeah]

Target.Value = "O"

alternative, Marlett
Target.Value="r"

[RJ1969]

Hi ProtonLeah,

"O" is what I was looking for.
What alternative, Marlett? I tried "r" but it is not the X.

Thanks.

[RJ1969]

The x is a little small. Can it make a capital letter X ?
Or maybe add a font size?
like with this one Target.Font.Bold = True

Thank you

[RJ1969]

I am attaching the file .
Thanks :o)

[bakerman2]

How about this.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'create 'toggle' status for cells L16 & O16 & F27 & L27 - set check mark in cell.
On Error Resume Next
Dim iColor As Integer
With Target
    If .Address = "$L$10" Or .Address = "$L$12" Then
        iColor = .Interior.ColorIndex
        If iColor < 0 Then
            iColor = 6
        Else
            iColor = iColor + 6
        End If
        Cells.FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
        .FormatConditions(1).Interior.ColorIndex = iColor
        .Font.Name = "Marlett"                '<<<< set Font to Winddings 2.  A 'P' in that Font displays a check mark.
        .Font.Bold = True                         '<<<< set Font to 'Bold'
        .Font.ColorIndex = 3                      '<<<< set Font color to Red
        If .Value = "" Then
            .Value = "9"
            .Offset(, 2).Select
        Else
            .Value = "r"
        End If
    End If
End With
End Sub

[RJ1969]

Hi Bakerman2,
I need the check mark in L10, L12, etc. and I will use this code (marlet) for the C8,C14,etc.

[RJ1969]

Hi Bakerman2
When I click the box to uncheck, the X stayed in the box. It supposed to check and uncheck the box.

thanks.

[RJ1969]

something like this.
And I like you Marlett font as an "X"
see attached.

[bakerman2]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'create 'toggle' status for cells L16 & O16 & F27 & L27 - set check mark in cell.
On Error Resume Next
Dim iColor As Integer
With Target
    If .Address = "$L$10" Or .Address = "$L$12" Then
        iColor = .Interior.ColorIndex
        If iColor < 0 Then
            iColor = 6
        Else
            iColor = iColor + 6
        End If
        Cells.FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
        .FormatConditions(1).Interior.ColorIndex = iColor
        .Font.Name = "Marlett"                '<<<< set Font to Winddings 2.  A 'P' in that Font displays a check mark.
        .Font.Bold = True                         '<<<< set Font to 'Bold'
        .Font.ColorIndex = 3                      '<<<< set Font color to Red
        If .Value = "" Then
            .Value = "r"
            .Offset(, 2).Select
        Else
            .Value = ""
            .Offset(, 2).Select
        End If
    End If
End With
End Sub

[RJ1969]

If Marlett "r" for bolded "X" then what is the bolded check mark?

[bakerman2]

For checkmark it's "b"

[RJ1969]

Oh ok.

For slight check mark on Windings 2 the letter is "P" but for the HEAVEY CHECK MARK what letter should I used or characters?

[bakerman2]

WingDings 1 and ChrW(10004)

[RJ1969]

Hi
Is this right? After I added it then when I click the box, the ChrW(1004) text is what shows in the box instead a check mark.

Also, If you can fix this section, please:

If Not Intersect(Target, Range("C8,C14,C20,C26,C32,C38,C41,C44")) Is Nothing Then

When I added this, it does not uncheck the box it is just stay in the box.

This is the original with a single range:

If Target.Address(0, 0) = "C8" Then
Target.Font.Name = "WingDings 1"
Target.Font.Bold = True
Target.Font.ColorIndex = 3
If Target.Value = "" Then
Target.Value = "ChrW(1004)"
'Range("L16").Value = ""
Target.Offset(0, 1).Select
Else
Target.Value = ""
Target.Offset(0, 1).Select
End If
End If

This is where I added more range cells:

If Not Intersect(Target, Range("C8,C14,C20,C26,C32,C38,C41,C44")) Is Nothing Then
'If Target.Address(0, 0) = "C8" Then
Target.Font.Name = "WingDings 1"
Target.Font.Bold = True
Target.Font.ColorIndex = 3
If Target.Value = "" Then
Target.Value = "ChrW(1004)"
'Range("L16").Value = ""
Target.Offset(0, 1).Select
Else
Target.Value = ""
Target.Offset(0, 1).Select
End If
End If

[protonLeah]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    Dim iColor As Long
'--------  highlight selected cell
    With Target
        iColor = .Interior.ColorIndex
        If iColor < 0 Then
            iColor = 6
        Else
            iColor = iColor + 6
        End If
        Cells.FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
        .FormatConditions(1).Interior.ColorIndex = iColor
    End With
'---------
    'create 'toggle' status for cells L16 & O16 & F27 & L27 - set **check mark** in cell.
    If Not Intersect(Target, Range("L10,L12,L16,O16,F27,L27")) Is Nothing Then
        With Target
            Application.ScreenUpdating = False
            .Font.Name = "Wingdings 2"
            .Font.Bold = True
            .Font.ColorIndex = 3
            If .Value = "" Then
                .Value = "P"                   'displays a check mark.
                .Offset(, 2).Select
            Else
                .Value = ""
                .Offset(, 2).Select
            End If
        End With 'target
        Application.ScreenUpdating = True
        Exit Sub
    End If
'-------------------
'"r" in that Font displays an "X"
If Not Intersect(Target, Range("C8,C14,C20,C26,C32,C38,C41,C44")) Is Nothing Then
        Application.ScreenUpdating = False
        With Target
            .Font.Name = "Marlett"
            .Font.Bold = True
            .Font.Color = vbRed
            If .Value = "" Then
                .Value = "r"
                .Offset(0, 2).Select
            Else
                .Value = ""
                .Offset(0, 2).Select
            End If
        End With 'target
        Application.ScreenUpdating = True
        Exit Sub
    End If
End Sub

[bakerman2]

And for the HEAVY CHECKMARK.

    'create 'toggle' status for cells L16 & O16 & F27 & L27 - set **check mark** in cell.
    If Not Intersect(Target, Range("L10,L12,L16,O16,F27,L27")) Is Nothing Then
        With Target
            Application.ScreenUpdating = False
            .Font.Name = "Wingdings 1"
            .Font.Bold = True
            .Font.ColorIndex = 3
            If .Value = "" Then
                .Value = ChrW(10004)                   'displays a check mark.
                .Offset(, 2).Select
            Else
                .Value = ""
                .Offset(, 2).Select
            End If
        End With 'target
        Application.ScreenUpdating = True
        Exit Sub
    End If

[RJ1969]

Hi
Thank you to both of you. It did exactly what I wanted to achieved.
There is another issue that I came about with that is When I added the code in Thisworkbook and it had a problem with the X mark only. The check mark is no problem on it.

This is the code I added in Thisworkbook:

Private Sub Workbook_SheetChange(ByVal _
Sh As Object, ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
If Intersect(Target, Range("AF11,X18")) Is Nothing Then
Target = UCase$(Target)
Else
Target = StrConv(Target, vbProperCase)
End If
Application.EnableEvents = True
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveCell.FormatConditions.Delete
End Sub

Please see attachment.

Thanks :o)

[RJ1969]

Good morning

I was able to resolve this issue. I use the Windings Unicode for X mark.
Thank you to both of you and all the expert who's always there for all the people who's no experience with excel especially me :o) .... just a little.

Again, Thank you.

[bakerman2]

Good luck with your project and thanks for rep+.

[RJ1969]

Hi Bakerman,
Thank you too. I do really appreciate all the help.

I've been thinking and I've been wanted to try this in some of my worksheet. I don't know if this is possible.
I wanted to add an images (3d box) and be able to click over it and the "x" or check mark appears. The worksheet will be protected.

If it's not possible, is there anyway to make the cell look like it? (See attachment).

Thank you.

[RJ1969]

Hi
Guess what? I merged a cell D to cell N and the colortab in this particular is not working.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    Dim iColor As Long
'--------  highlight selected cell
    With Target
        iColor = .Interior.ColorIndex
        If iColor < 0 Then
            iColor = 6
        Else
            iColor = iColor + 6
        End If
        Cells.FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
        .FormatConditions(1).Interior.ColorIndex = iColor
    End With
'---------

[RJ1969]

Morning :o)
I attached the file which the merged cell D:N and other merged cell are skipping when you color tab it.

Thanks.

[RJ1969]

Oooops... one more issue came about.
While the sheet is protected, try to enter an item number in cell D and press tab keyor the enter key and the check box marked it which i don’t select to mark it that box.
I tried to search for a tab/enter to disabling it but it seems does not work. Maybe you have a better solution.

Is there a way to place a message box if the user try to use tab key or enter key “Pls. Use mouse pad to click box”
Something like this.

Thank you

[protonLeah]

...I merged a cell D to cell N...

The line:

If Target.Count > 1 Then Exit Sub

Causes the macro to exit if more than one cell is selected.

[RJ1969]

Hi
I took that part out and it look like it work.

After I enter the Item number, it jump to the check box and it automatically check mark the box. I am trying to figure it out to skip those boxes.

Thanks.

[RJ1969]

Do I really need this code in it?
If Target.Count > 1 Then Exit Sub

I am not sure how much important it is to be in it. Should I put it back?

[RJ1969]

Hello
I pretty much got everything I needed from my previous request.
Everything works fine until I almost achieved what I needed until I added a “RESET” button and I get the Run Time Error 13 (mismatch).

Can you please fix the code so I will be able to do a Reset Button.

Thank you in advance :o)

[bakerman2]

Sub Reset_Click()
Application.EnableEvents = False
[J5,C8,C4,C20,C26,C32,C38,C41,C44,L10,L12,L16,O16,F27,L27].Value = ""
Range("J5").Select
Application.EnableEvents = True
End Sub

[RJ1969]

Hi Bakerman2
Almost there :o)
I attached the file again.
When I click Reset button. The colortab stayed in a cell N10.

Please check. Thanks :o)

[bakerman2]

How about this.

Sub Reset_Click()
Application.EnableEvents = False
Cells.FormatConditions.Delete
[J5,C8,C14,C20,C26,C32,C38,C41,C44,L10,L12,L16,O16,F27,L27,N10,N12].Value = ""
Range("J5").Select
Application.EnableEvents = True
End Sub

[RJ1969]

Hi Bakerman2
I think its perfect :o)
Although when it returns to cell J5, it shows white color instead yellow.

Do you think you can change the font in the message box Maybe Red font and the style? Is this possible? If not, no problem.

Thanks.

[bakerman2]

The moment I hit Tab-key colored tab disappears and never comes back.

How do you invoke it and whats the purpose ?

Font and Font style can't be changed in a MessageBox. You would have to create a userform with a Label and Buttons to achieve that.

[RJ1969]

Hi
The purpose is that wihen I hit the button Reset and it return to J5 cell and it should highlight the the cell and this will tell me or my user where the cursor is. Is like when you tab and it highlights the cursor every time u hit the tab.
I particularly like this settings because i have a little problem with my eyesight and it is easy for me to see and know where i am at in the position :o)
But if is no solution, it is ok. Because when i hit the the tab it still works.

About the font for the message box, I thought so. I kinda did my research in the net. Thanks for the findings.

Let me know if u a solution w the color tab in J5 but if none no worry.

Thanks (friend)

[bakerman2]

This should do it.

[RJ1969]

Hi Bakerman2
Yay!!!! I knew you will find a solution :o)

What did you do in order to work?

Thank you

[bakerman2]

Check Reset button code.

[RJ1969]

Ok.... :o)
Thank you so much and I do appreciate the time you gave to it.

[bakerman2]

Glad to help and thanks for rep+. Much appreciated.

Good luck with your project.

[RJ1969]

Please...... I have a very last request with this project and hoping it is possible.
I just thought of it that maybe it is possible.

I want to be able to click the boxes for X mark and check mark although the cells are protected?

cells boxes protected:
C8,C14,C20,C26,C32,C38,C41,C44,L10,L12,L16,O16,F27,L27

Cells not protected:
J5, N10 and N12

[bakerman2]

The last one.

[RJ1969]

Hi

I have an issue with the colortab and also if I put a password and click the reset button it ask for the password. My user will not have the password.

Thanks

[RJ1969]

When I protected the worksheet with a password, I normally select “unlocked cells” and “format cell” in order. Will you be able to set it this way and still be able to Tick the box?
I promise this will be the end after this is solved :o(

[bakerman2]

Try this. Password is RJ1969.

[RJ1969]

Hi Bakerman2
I tried the worksheet you sent me. It is not exactly what I wish for but close enough :o( sorry.
When I press the enter key, it still passing all the boxes. This is what I am trying for my user to avoid to press the enter key and will accidentally to have a wrong check mark.
I want something for them to be able to use tab key and enter key but only in the unlock cell.

See the attachment. I have unlock all entire cells including all the boxes in the worksheets except J5, N10, N12.
If you press the enter key or the tab key, it only goes in the J5, N10, and N12 cells with a color tab. The color tabbing is good (I love it :o).

But if it's not possible to tick the box with lock cell, no problem my friend. I think I will stick in the old format if is not possible. But I am hoping it will work the way I have wish for. So let me know and I hope explain it very well since my English is not good as it is.


Thanks.

[bakerman2]

Not possible what you're after.

I know you are trying to create a dummy-proof sheet but you will have to educate your user to correctly use the sheet.

When there's human interaction there always bound to be margin for error. That's just the way it is.

[RJ1969]

Good morning :o)
Ok... I guess I'll just have to do that, just educate them. Like they said "it is what it is" :o)
Thank you for all the help! You're the best as always.

Again, Thank you.

[RJ1969]

I attached the file.

[RJ1969]

Hi Bakerman2,
Sorry to bother you again. I have been searching and I think I found the answer.

Remember the issue that I was having and I think I found something to make my worksheet work. This is just exactly what I was looking for.
All I just need is to fix two things on it.

1)
no color tab or when I press the tab key or the enter key on (J4,AI5,R38,U41,Q44), can you please a color tab on it.

2)
can you add a message box after J4 entry, maybe “Don’t forget to click box”.

Everything is good except those 2 things.

My reset button is good.

The check boxes when I click is good except when I am at the bottom to click the box it return to J4 cell and I have to scroll down to see if the box is being check. I keep scrolling down every time I click the box at the end of the page. After you click the box, it should offset 2 according in the code. If you can fix this is as well that will be great.

Thanks.

[RJ1969]

Hi Bakerman2
I was able to add the message box into it.
Minus -1 :o)

Hope you'll be able to fix the color tab in cell J4",AI5, R38, U41, Q44.
Here is the updated file with the the message box.

Thanks.

[RJ1969]

Hi Bakerman
Any luck?

[Winon]

Hello RJ1969,

The check boxes when I click is good except when I am at the bottom to click the box it return to J4 cell and I have to scroll down to see if the box is being check. I keep scrolling down every time I click the box at the end of the page. After you click the box, it should offset 2 according in the code. If you can fix this is as well that will be great.

It can be done, but then you may loose the "Tab" order. Is that o.k. with you?

Hope you'll be able to fix the color tab in cell J4",AI5, R38, U41, Q44.

How do you want it "Fixed"? what must it be changed to?

Regards.

[RJ1969]

Hi,
I don’t really want to lose the tab order.

I want to fixed the cells to have a color tab.
Let me clear or explain it well. The cells J4,AI5,R38,U41,Q44 should be white not gray color. If u press the tab and it goes to the cell then it should be yelow.
Let say you tab to R38 and this cell should be uellow not white.

Thanks

[bakerman2]

You are asking too many conflicting things. You want a fix above a fix above a fix.

Your SelectionChange-event kicks in 3 times for 1 run. In a normal workbook Tab and Enter also fires SelectionChange, in your file it doesn't anymore.

You end up at the top of the sheet because your SelectionChange code says to on every run. So you need to scroll down to look at Checkbox. Now you want a fix to overrule your SelectionChange code.

You cannot keep fixing 1 thing with another. Result is that nothing works properly anymore.

This needs to be thought over again from scratch to work properly.

[RJ1969]

Hi
Ok. I will try to start from scratch with a different method.
In the meantime I will use what I have although there is a bit of a issue.

Thank you all.