Simulate a checkbox within a cell

[ABabeNChrist]

I’m trying to transform a cell to give the appearance of being a checkbox. By using a Worksheet_BeforeDoubleClick to insert a Bold/ Capitalized X.
The checkboxes provided with excel are to small
or is there a better way to achive this.
I search around trying to find any ideas
I put what small knowledge i have and this is what i was able to get, I'm probabaly off by a mile.

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Range("F9"), Target) Is Nothing Then
        Cancel = True
        If ActiveSheet.Range("F9").Value = " " Then
        Else
           ActiveSheet.Range("F9").Value = "X"
        End If
    End If
End Sub

[HansV]

Your code won't clear the "X" the second time the user double-clicks F9. Here is a corrected version:

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Range("F9"), Target) Is Nothing Then
        Application.EnableEvents = False
        Cancel = True
        If Range("F9").Value = "" Then
           Range("F9").Value = "X"
        Else
           Range("F9").Value = ""
        End If
        Application.EnableEvents = True
    End If
End Sub

[HansV]

If you set the font of cell F9 to Wingdings (a font distributed with Windows, so available on all Windows PCs), you can use the empty and filled check box characters from that font:

x128.png

See the attached sample workbook.

SimulateCheckbox.xlsm

[ABabeNChrist]

Thank you Hans
I guess I got closer than I thought, maybe I am learning something.

[VegasNath]

If you set the font of cell F9 to Wingdings (a font distributed with Windows, so available on all Windows PCs), you can use the empty and filled check box characters from that font:

Hans, I was curious as to how this could be used on a multi range. I attach my effort which is incorrect for your expert correction:

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim rng As Range
Set rng = Range("F9:F18")

    If Not Intersect(Range(rng), Target) Is Nothing Then
        Application.EnableEvents = False
        Cancel = True
        If Range(rng).Value = Chr(254) Then
            Range(rng).Value = Chr(168)
        Else
            Range(rng).Value = Chr(254)
        End If
        Application.EnableEvents = True
    End If
    
End Sub

[HansV]

In the first place, rng is already a Range object, so it makes no sense to use Range(rng).
In the second place, a multi-cell range doesn't have a single value, so you can't simply test rng.Value.
In general, you'd have to loop through the cells of the intersection, but since the user can only double-click a single cell, we can cut a corner and refer to Target here:

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim rng As Range
  Set rng = Range("F9:F18")
  If Not Intersect(rng, Target) Is Nothing Then
    Application.EnableEvents = False
    Cancel = True
    If Target.Value = Chr(254) Then
      Target.Value = Chr(168)
    Else
      Target.Value = Chr(254)
    End If
    Application.EnableEvents = True
  End If  
End Sub

[VegasNath]

Great, Thanks Hans, one for the collection!

How would one then count the checked / unchecked boxes? I tried:

=COUNTIF(F9:F22,Target.Value = Chr(168))

which returns 0.

[HansV]

You're mixing up a cell formula (COUNTIF) and VBA (Target.Value). Where do you want to count this? In a formula or in VBA?

[VegasNath]

In a formula please.

[HansV]

To count the number of unticked boxes:

=COUNTIF(F9:F22,CHAR(168))

To count the number of ticked boxes:

=COUNTIF(F9:F22,CHAR(254))

This assumes that all cells contain either an unticked or a ticked box.

[VegasNath]

[ABabeNChrist]

When using this method is there a limit of how many target cells I can use

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim rng As Range
  Set rng = Range("F9:F18")
  If Not Intersect(rng, Target) Is Nothing Then
    Application.EnableEvents = False
    Cancel = True
    If Target.Value = Chr(254) Then
      Target.Value = Chr(168)
    Else
      Target.Value = Chr(254)
    End If
    Application.EnableEvents = True
  End If  
End Sub

[HansV]

No, you could specify an entire column, or several, for example:

Set rng = Range("F:F,L:L")

if you want check boxes in all cells in columns F and L.

[ABabeNChrist]

Thank you Hans

[ABabeNChrist]

I can not seem to get a large amount of cell to work
here is the list of cells i was trying to get to work.

B10,B16,B21,B28,B36,B43,B50,B56,B62,B67,B77,B83,B89,B99,B107,B129,B151,B158,B170,B177,B196,B203,B209,B214,B219,B225,B230,B235,B240,B246,B251,B257,B262,B278,B284,B291,B298,B305,B312,B317,D10,D16,D21,D28,D36,D43,D50,D56,D62,D67,D77,D83,D89,D99,D107,D129,D151,D158,D170,D177,D196,D203,D209,D214,D219,D225,D230,D235,D240,D246,D251,D257,D262,D278,D284,D291,D298,D305,D312,D317,F10,F16,F21,F28,F36,F43,F50,F56,F62,F67,F77,F83,F89,F99,F107,F129,F151,F158,F170,F177,F196,F203,F209,F214,F219,F225,F230,F235,F240,F246,F251,F257,F262,F278,F284,F291,F298,F305,F312,F317,H10,H16,H21,H28,H36,H43,H50,H56,H62,H67,H77,H83,H89,H99,H107,H129,H151,H158,H170,H177,H196,H203,H209,H214,H219,H225,H230,H235,H240,H246,H251,H257,H262,H278,H284,H291,H298,H305,H312,H317

I can break the number of cell to at least half and they work fine. I have been messing with this for a while, cant seem to figure what I'm doing wrong.

UPDATE: I am able to use
Set rng = Range("B10:B317,D10:D317,F10:F317,H10:H317")
but then of course I get everything between

[HansV]

Your string works OK when I try it, even if I add more cells...

[ABabeNChrist]

its been a long day, I think I'll get some rest first, my brains running a little slow right about now.