Creating/Editing Checkboxes with VBA

[Nyanko]

Hi,
I didn't think this would be difficult, but I've tied myself in knots with Google searches and am not proper confused !!

I have a range of cells : A1:A10
I would like every cell in this range alone to have a check box
Each check box should link to it's respective cell: A1, A2 etc..
Each check box should be named for it's respective cell : CheckBoxA1, CheckBoxA2 etc...

I will need to adapt this code to allow further ranges to be added B1:B10, C1:C10 etc so this will eventually become a loop.

Can someone advise if a) this is possible and b) a pointer on where to start ... I have gotten as far as

LastRow = Range("A65536").End(xlUp).Row

For a = 6 To LastRow
    Cells(a, 1).Select
    ActiveSheet.CheckBoxes.Add(425, 305, 25, 30).Select
    ActiveSheet.Shapes.Range(Array("CheckBox A1")).Select
    ActiveSheet.Shapes("CheckBox A1").Name = "CheckBox A1"
    Selection.Name = "CheckBox A1"
    With Selection
        .Value = xlOff
        .LinkedCell = "A1"
        .Display3DShading = False
    End With
Next a

but this breaks really easily as I'm trying to manually pick up the checkbox name

 

[RickXL]

Hi,

I thin k you are trying to do this:

Sub Test()

    Dim i As Long
    Dim LastRow As Long
    Dim r As Range
    Dim chk As Object

    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 6 To LastRow
            Set r = .Cells(i, 1)
            Set chk = .CheckBoxes.Add(r.Left, r.Top, r.Width, r.Height)
            With chk
                .Name = "Checkbox" & r.Address(False, False)
                .Caption = "Chkbox " & r.Address(False, False)
                .Value = xlOff
                .LinkedCell = r.Address
                .Display3DShading = False
            End With
        Next
    End With

End Sub

In stead of bulk populations of controls I find it easier to use right-clicking a cell to change its state. You can put ticks and crosses in the cell or just change the colour. The whole thing can be controlled from a Worksheet_Change macro.

Insert the following macro in the appropriate sheet module to try it out.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B1:F5")) Is Nothing Then
        With Target
            If .Value = "X" Then .Value = "" Else .Value = "X"
        End With
        Cancel = True
    End If
End Sub

After inserting the macro try a right-click anywhere in the range B1:F5

 

[Nyanko]

Hi,
In stead of bulk populations of controls I find it easier to use right-clicking a cell to change its state. You can put ticks and crosses in the cell or just change the colour. The whole thing can be controlled from a Worksheet_Change macro.

Insert the following macro in the appropriate sheet module to try it out.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B1:F5")) Is Nothing Then
        With Target
            If .Value = "X" Then .Value = "" Else .Value = "X"
        End With
        Cancel = True
    End If
End Sub

After inserting the macro try a right-click anywhere in the range B1:F5

Mind. Blown.
You have just made my life a billion times easier and the end user now thinks I am a wizard. Thank you so much for taking the time to suggest this

 

[RickXL]

Hi,

No problem.

I always prefer "easier" when it comes to programming.

I am glad you could make use off it and thanks for the feedback.

Regards,