none
How enter checkmark with VBA in Excel, then strikethrough adjacent cells RRS feed

  • Question

  • Hi, I'm creating a checklist and wanted to be able to check items off with double click checkmark in one column, plus strikethrough on the actual items in the adjacent columns.

    I found a VBA script for that on another MS forum that works great for the checkmark: double click on and off. But I I can't find an example that works for the strikethrough.

    I would also like to remove the strikethrough with the double click, same as the checkmarks.

    I have looked at the conditional formatting with form controls and/or a value like "done" or "Yes", but nothing that will do it by building on the VBA code (below). I really disklike using the form control.

    Current code for checkmark:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        On Error Resume Next
        If Not Intersect(Target, Range("B3:B37,F3:F25")) Is Nothing Then
            If Target.Count > 1 Then Exit Sub
            Set r = Target
            If r.Value = "" Then
                r.Value = ChrW(10004)
                With r
                    .HorizontalAlignment = xlCenter
                    With .Font
                        .Name = "Arial"
                        .Size = 14
                        .Color = vbGreen
                    End With
                End With
            Else
                r.Value = ""
            End If
        End If
        Cancel = True
    End Sub


    Tuesday, November 12, 2019 9:32 PM

All replies

  • The following version will strikethrough the two cells to the right of the target. You can change this in the code by increasing or decreasing the 2 in Resize(1, 2)

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        On Error Resume Next
        If Not Intersect(Target, Range("B3:B37,F3:F25")) Is Nothing Then
            With Target
                If .Value = "" Then
                    .Value = ChrW(10004)
                    .HorizontalAlignment = xlCenter
                    With .Font
                        .Name = "Arial"
                        .Size = 14
                        .Color = vbGreen
                    End With
                    .Offset(0, 1).Resize(1, 2).Font.Strikethrough = True
                Else
                    .Clear
                    .Offset(0, 1).Resize(1, 2).Font.Strikethrough = False
                End If
            End With
            Cancel = True
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, November 12, 2019 9:47 PM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi Zhang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Wednesday, November 13, 2019 7:26 AM
  • Thank you, exactly what I need!
    Wednesday, November 13, 2019 9:23 PM