check box Error

[John]

Here is the code I found on this site... and get and error on the last
line... starts with Shapes(Shapes.Count......

Any Ideas? Thanks so much!

Sub AddCheckBoxes()

'Add A Forms CheckBox

Dim Cell As Range
Dim Col As String
Dim LinkCol
Dim R As Long
Dim Value

Col = "B"
Value = "Mark"
LinkCol = "D"

For R = 1 To ActiveSheet.Cells(Rows.Count, Col).Row
Set Cell = ActiveSheet.Cells(R, Col)
If Cell.Value = Value Then
With ActiveSheet.Shapes
..AddFormControl xlCheckBox, Cell.Left, Cell.Top, Cell.Width,
Cell.Height
End With

Shapes(Shapes.Count).ControlFormat.LinkedCell = Cell.(R,
LinkCol).Address
End If
Next R

End Sub

 

[Dave Peterson]

You could try changing:

Shapes(Shapes.Count).ControlFormat.LinkedCell ...
to
Activesheet.Shapes(activesheet.Shapes.Count).ControlFormat.LinkedCell ...

But here's another version (from a saved post):

Here are two subroutines. The first one adds a bunch of checkboxes from the
Forms toolbar to a range in the activesheet (b3:B10).

The second one adds one to the cell to the right (C3:C10) each time you check
the box.

The first one only needs to be run once--to set up the checkboxes on the
worksheet.

Option Explicit
Sub testme()

Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete
For Each myCell In ActiveSheet.Range("B3:B10").Cells

With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
'.LinkedCell = myCell.Offset(0, 10).Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
'.OnAction = "'" & ThisWorkbook.Name & "'!dothework"
End With
.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

Sub DoTheWork()
Dim myCBX As CheckBox
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

If myCBX = xlOn Then
'do something
Else
'do something else
End If

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You may not need the macro DoTheWork. You can delete it and delete the
..onaction line, too.

 

[John]

Thanks Dave, got this one to work

You could try changing:

Shapes(Shapes.Count).ControlFormat.LinkedCell ...
to
Activesheet.Shapes(activesheet.Shapes.Count).ControlFormat.LinkedCell ...

But here's another version (from a saved post):

Here are two subroutines. The first one adds a bunch of checkboxes from the
Forms toolbar to a range in the activesheet (b3:B10).

The second one adds one to the cell to the right (C3:C10) each time you check
the box.

The first one only needs to be run once--to set up the checkboxes on the
worksheet.

Option Explicit
Sub testme()

Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete
For Each myCell In ActiveSheet.Range("B3:B10").Cells

With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
'.LinkedCell = myCell.Offset(0, 10).Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
'.OnAction = "'" & ThisWorkbook.Name & "'!dothework"
End With
.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

Sub DoTheWork()
Dim myCBX As CheckBox
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

If myCBX = xlOn Then
'do something
Else
'do something else
End If

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You may not need the macro DoTheWork. You can delete it and delete the
..onaction line, too.