Organizing check boxes

[axelg]

I have a small script that creates a checkbox in the selected cells.

Sub addcheckboxes()

    Dim cb As Object
    Dim cell As Range
    
    For Each cell In Selection
            
        Set cb = ActiveSheet.CheckBoxes.Add(cell.Left + 5, cell.Top - 1, 20, 14.4)
        cb.Caption = ""
        cb.LinkedCell = cell.Address
        cell.NumberFormat = ";;;"
                

    Next cell
    
End Sub

I would like to have the checkbox always in the middle of my cell and have this even if I change the cell format.
What can I add as arguments to have this? (in the above, this worked for a few cells for me but I always have to fool around to have it correct for the cells I want my checkboxes in)

Thanks

 

[mikerickson]

Instead of adding controls you could have the whole cell treated like a checkbox.

Put this in the sheet's code module and double click on cells in B2:B20 to see what I mean

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    With Target
        If Not Application.Intersect(.Cells, Range("B2:B20")) Is Nothing Then
            Cancel = True
            If .Value <> vbNullString Then
                .Value = vbNullString
            Else
                .Value = "a"
                .Font.Name = "Marlett"
                .Font.Size = 14
                .HorizontalAlignment = xlCenter
            End If
        End If
    End With
End Sub

If you don't want to use Marlett check boxes (above) then you could add code like this to yours

cb.Top = cell.Top + (cell.Height - cb.Height) / 2
cb.Left = cell.Left + (cell.Width - cb.Width) / 2
cb.Placement = xlMove

Your difficulty is that controls don't respond to Placment being xlMoveAndSize so that if you resize the cell, the checkbox will do nothing.
Marlett checkboxes are the cell so they do respond to changing cell size

 

[axelg]

Thanks, will try that.
So if I understand coorectly, i just need to paste the above in the module of the workbook where I want those cells.
How do I choos which sheet he puts them on ? (As I understand it's B2 to B20)


What's the difference between sub and private sub ?

 

[mikerickson]

Put the Marlett checkboxes on what ever sheet needs them.