multiple check box change variable

[scott23]

Hi, i found an earlier post containing the following macro below that
should allow me to create multiple checkboxes in 1 column going down
from row 5-60. However for each one i want the cell it references to
increase by 1 and match the row for the checkbox.

The only problem is that i dont know what to do with this. I want to
create multiple checkboxes going down from D5 to D55 and referencing
E5 to E55. Where do i input this macro ? Do right click on the
worksheet name and paste it in the 'view code' ? Or should i create a
macro and assign it to the 1st checkbox and then just copy and paste
50 times ? Or is there another way that was intended for this code.
Thanks ... sorry to sound so ignorant, but im a newbie to macros.
sg

Here is the code:

Sub DuplicateCBs()
Set z = ActiveSheet.DrawingObjects("Check Box 1") 'assumes
first one
is there, linked to e5.
For i = 5 To 55
z.ShapeRange.Duplicate.Select
Selection.ShapeRange.IncrementLeft -12#
Selection.ShapeRange.IncrementTop 10.5
Selection.LinkedCell = "$e$" & i + 1
Set z = Selection
Next
End Sub

 

[Dave Peterson]

The code would go into a General module--not under a worksheet or ThisWorkbook.

You can rightclick on the project (in the VBE) and Insert|Module.

Paste it there.

And this looks like a "run once" macro. So you wouldn't assign it to the
checkbox.

Instead, right after you paste it into the code window, you can go back to the
worksheet and do Tools|macro|macros and select DuplicateCBs. Then click Run.

This assumes that the activesheet has a checkbox (from the Forms toolbar) named
"Check Box 1" on it.

After you run it, you could choose to delete the macro (or keep it for future
use on different sheets).

Another way to do it is to just add them (not copy from an existing checkbox):

Option Explicit
Sub addCBX()
Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete 'nice for testing
For Each myCell In ActiveSheet.Range("D555").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Offset(0, 1).Address(external:=True)
.Caption = "" 'or whatever you want
End With
End With
Next myCell
End With
End Sub