Create Checkbox and link to relative Cell

[MS Forum Newsgroup User]

Hi,

I have a script that I am using that creates a checkbox in a range but am
having problems trying to create this for the relative cell and linking this
to a relative row cell,

i.e. if the current cell is R16 create a checkbox and link the value to S16.

Sub CellCheckbox()
Range("R15").Offset(1, 0).Select
For i = 1 To 50
'add the checkbox
ActiveSheet.CheckBoxes.Add(646.5, 203.25, 24, 17.25).Select
ActiveSheet.Shapes("Check Box 1212").Select
Selection.Characters.Text = ""
With Selection
.Value = xlOff
.LinkedCell = "$X$17"
End With
Next i
End Sub

 

[Dave Peterson]

It looks like you want to put checkboxes in 50 cells (R15:R64).

If that's true...

Option Explicit
Sub CellCheckbox()
Dim myCell As Range
Dim myRng As Range
Dim CBX As CheckBox

'if the current cell is R16 create a checkbox
'and link the value to S16.

'50 cells starting in R16???
With ActiveSheet
.CheckBoxes.Delete 'nice for testing!
Set myRng = .Range("R16:R65")
End With

For Each myCell In myRng.Cells
With myCell
Set CBX = .Parent.CheckBoxes.Add _
(Top:=.Top, _
Left:=.Left, _
Width:=.Width, _
Height:=.Height)
CBX.Name = "CBX_" & .Address(0, 0)
CBX.Caption = "" 'or what you want
CBX.Value = xlOff

'I like to use the same cell as the linked cell
'with a number format of ;;;
'I can't see the true/false in the worksheet, but it
'still appears in the formula bar
CBX.LinkedCell = .Offset(0, 1).Address(external:=True)
.Offset(0, 1).NumberFormat = ";;;"
End With
Next myCell
End Sub

 

[Dave Peterson]

I wrote r15:r64, but used r16:r65. Change the code if you need to.

 

[MS Forum Newsgroup User]

Dave.

That's Excellent - thanks!

Thanks

I wrote r15:r64, but used r16:r65. Change the code if you need to.