Adding and linking ActiveX check boxes to multiple cells

Question

Hello, 

Apologies as I am new to VBA and I know this must be quite a simple problem... I have an excel workbook with multiple worksheets. In each worksheet there is a column in which I would like to add an activeX control check box to each cell, link it to the cell it is in and have the checkbox appear in the centre of the cell. 

I have tried all the VBA code I can find on the internet but nothing has achieved this and I have had many unsuccessful attempts at editing them to make it fit. 

Please let me know if you can help at all. 

Thanks, 

Barry

Note: I have been able to do this with the form control checkboxes but I want to be able to do it with the activeX type (as the form control type seem to bunch up when I filter, even when set to move with the cell).

Answer 1

Re:  checkboxes on worksheet

Since this is your second time around with this issue, I propose an alternative...

In each sheet's code module, where checkboxes are required, place this code...

'---

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

'Exit if Doubleclick is not on Column E    <<<<

 If Target.Column <> 5 Then Exit Sub

'Toggle Checkmark

 With Target

  'chr(163)

   If .Value = "£" Then

      .Value = "R"

   Else

      .Value = "£"

   End If

  .Font.Name = "Wingdings 2"

  .HorizontalAlignment = xlCenter

 End With

'Exit edit mode

 Cancel = True

End Sub

'---

Suggest you increase the font size and set cells to Bold in the applicable column.

When a cell in the column is double-clicked the "checkbox" will toggle unchecked/checked.

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)

Answer 2

Thank you! This is a great solution! Thanks again :D