Sub LinkCheckBox() not recognised?

Question

Hi,

trying to create a checklist project timeline and obviously don't want to click 200 something times to link my checkbox macros to cells, so found this code online:

Sub LinkCheckBoxes()

Dim chk As CheckBox

Dim lCol As Long

lCol = 3 'number of columns to the right of checkbox

For Each chk In ActiveSheet.CheckBoxes

With chk

.LinkedCell = _

.TopLeftCell.Offset(0, lCol).Address

End With

Next chk

End Sub

However I keep getting an identifier error and its flagging up linkcheckboxes as the issue?

Answer 1

Hi Learninggamer,

I'm Michelle and I'd be glad to help you out with your query or concern.

There's a formula shared here to resolve the issue you're dealing with:

' this code goes into a module

Option Explicit

Dim CheckBoxes() As New ChkClass
Const numChkBoxes = 20
'

Sub doCheckBoxes()
makeCheckBoxes
activateCheckBoxes
End Sub

Sub makeCheckBoxes() ' creates a column of checkBoxes

Dim *** As Worksheet
Set *** = ActiveSheet

Dim i As Integer
For i = 1 To ***. Shapes.Count
' Debug.Print ***. Shapes(1). Properties
***. Shapes(1). Delete
DoEvents
Next i

Dim xSize As Integer: xSize = 2 ' horizontal size (number of cells)
Dim ySize As Integer: ySize = 1 ' vertical size

Dim t As Range
Set t = ***. Range("b2"). Resize(ySize, xSize)

For i = 1 To numChkBoxes
***. Shapes.AddOLEObject ClassType:="Forms.CheckBox.1", Left:=t.Left, Top:=t.Top, Width:=t.Width - 2, Height:=t.Height
DoEvents
Set t = t.Offset(ySize)
Next i

End Sub

Sub activateCheckBoxes() ' assigns all checkBoxes on worksheet to ChkClass.ChkBoxGroup

Dim *** As Worksheet
Set *** = ActiveSheet

ReDim CheckBoxes(1 To 1)

Dim i As Integer
For i = 1 To ***. Shapes.Count

ReDim Preserve CheckBoxes(1 To i)
Set CheckBoxes(i). ChkBoxGroup = ***. Shapes(i). OLEFormat.Object.Object

Next i

End Sub

Reference: https://stackoverflow.com/questions/46503898/event-triggered-by-any-checkbox-click

Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.

I hope this information helps you. If you have any questions, please do let me know and I'll be more than happy to assist you further with this.

Best Regards,

Michelle

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.