Checkbox Macro

Question

Is there a macro for checkboxes that will link the cell to the right?  Something that would allow me to then copy that checkbox and paste to multiple locations and have it affect only the cell next to that checkbox.

Moved from: Office / Excel / Windows 10 / Office 2016

Answer 1

I made an edit to the post to clarify.  The main question is how to link a checkbox with a cell to the right using a macro.  Linking the cell will auto-generate a TRUE/FALSE response based on the status of the checkbox.  I was able to find the following that takes care of the issue in case this will help anyone else:

Sub LinkCheckBoxes()

Dim chk As CheckBox

Dim lCol As Long

lCol = 1 'number of columns to the right for link

For Each chk In ActiveSheet.CheckBoxes

   With chk

      .LinkedCell = _

         .TopLeftCell.Offset(0, lCol).Address

   End With

Next chk

End Sub

Answer 2

Hi,

I would suggest to add 'ActiveX' checkboxes (via vba macro)

so that when you change cell's dimensions, automatically, will change and checkbox size.

Sub Add_CheckBox_ActiveX()

'Oct 26, 2017

 Dim r As Range

 Dim sh

 For Each r In Selection

'

 'delete old shapes from selection range

 For Each sh In ActiveSheet.Shapes

 If Not Intersect(sh.TopLeftCell, r) Is Nothing Then sh.Delete

 Next

 '

'add checkbox

Set sh = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _

 DisplayAsIcon:=False, Left:=r.Left, Top:=r.Top, Width:=r.Width, Height:=r.Height)

 With sh

 .LinkedCell = r.Offset(, 1).Address

 .Placement = xlMoveAndSize

 '.Name = "cb " & r.Address

.Object.Value = False

.Object.Caption = ""

.Object.BackColor = RGB(153, 204, 255)

End With

'

Next

End Sub

NOTE

I assume that you select cells ONLY IN ONE COLUMN,

so that linked cells should be on the right.

Answer 3

Good job.

If you want code to add more checkboxes you can use this.

It creates checkboxes then links cell to the right.

No need to run your code in that case.

Sub Add_More_Checkboxes()

Dim myCBX As CheckBox

Dim myCell As Range

Dim myrng As Range

    Set myrng = Nothing

    On Error Resume Next

    Set myrng = Application.InputBox(prompt:="Select or Enter Range", Type:=8)

    Application.ScreenUpdating = False

    For Each myCell In myrng

    With myCell

        Set myCBX = .Parent.CheckBoxes.Add _

            (Top:=.Top, _

             Width:=12, _

             Left:=.Left + ((.Width - 12) / 2), _

             Height:=.Height)

            .RowHeight = 19

        With myCBX

            .LinkedCell = myCell.Offset(0, 1).Address(external:=True)

            .Caption = ""

            .Value = xlOff

        End With

    End With

    Next myCell

Application.ScreenUpdating = True

End Sub

Gord

Answer 4

Probably can be done.

What do mean by "edit the cell to the right"?

What sort of editing?

Gord

Answer 5

Hi,

Please clarify the question.

Assuming that checkbox is in cell A2, what are you expect in cell B2 (TRUE/FALSE) ?

also..

ActiveX or FormControl ?