Workaround to Create a Checkbox in a table with cells(A:A)

[garyj]

Hello;

This is not a question. I am writing it in case someone benefits.

I was trying to write a checkbox into a dropdown on a UserForm such as is possible with datasheets in Access. But I decided to work write off the sheet itself, which means finding a way to create a checkbox in the cell. The table lists training modules available, and by double-clicking the first cell in each row the user basically selects each module that is to be sent to the volunteers. The checkbox data is remembered because it is saved in that same cell, and can be referenced during code that populates a different spreadsheet with the new information (which volunteers get which training). I also wanted to do this dynamically so that a new row would create another instance of the checkbox.

Maybe someone has a code that will work, but here is my workaround.

So I format the column as Wingdings. I then added this code to that sheet.

Public Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim myval As Variant
    If Not Intersect(Range("IssueModule"), Target) Is Nothing Then
        myval = Target.Value
        If myval = "" Or myval = "¨" Then Target.Value = "þ"    ' "þ" in Wingdings looks like a checked box, while "¨" looks like unchecked box
        If myval = "þ" Then Target.Value = "¨"
        Cancel = True
    End If
End Sub

Okay, so it's a cheat, but only the coder knows that the cell is actually a Wingding view of special characters 168, and 254. The user will see this...



Screenshot.png

I think it is slick, and it works dynamically without extra code. But sometimes my 'slick' is built to need improvement at a later date. Does anyone foresee problems?

Gary

[Paul_Hossler]

Not exactly sure what you want since your code is for a double click event but you're using drop downs

1. One way is to use Arial MS Unicode font and Data Validation (Sheet1 in attachment)

Capture.JPG



2. Another is to use VBA to add checkboxs to the cells that 'need' them and just then the linked cell normally (Sheet2)

'https://stackoverflow.com/questions/14722444/excel-how-to-add-checkbox-with-vba-and-linking-to-cell-next-to-it
Sub Macro1()
    Dim r As Long
    Dim c As Range
    
    With Worksheets("Sheet2")
        For r = 2 To 5        '   5 last row
            Set c = .Cells(r, 1)
            c.Font.Color = vbWhite      '   hide TRUE or FALSE
            Call .CheckBoxes.Add(Left:=c.Left + 0.3 * c.Width, Top:=c.Top, Width:=0.7 * c.Width, Height:=c.Height).Select
            With Selection
                .Caption = ""
                .LinkedCell = c.Address
                .Name = c.Address
            End With
        Next r
    End With
End Sub

[garyj]

Not exactly sure what you want since your code is for a double click event but you're using drop downs

Thanks Paul, and both are good ideas. The trouble is the alterations that need to be done by the user in adding another course.

Clarification: What I had meant by dropdowns is that a user would select a course on a 2 column dropdown menu on a UserForm, the first button being a chkbox and the second the course. The dropdown would remain there until all selections are made and then the user would select 'OK'. As I saw various problems I opted to go straight from the sheet without a dropdown, and select instead from a checkbox.

I added a copy of my sheet on this, and you can double click on the first column. You can also add dynamically by typing in another course and it still works. The difference is that the code requires a check of the wingding value rather than true or false, but the user doesn't need to know that.

GaryDoubleCkCBox.xlsm