Checkboxes - Copying and Changing Cell Link Automatically

[mab1284]

I have a large spreadsheet with many tasks. Each task is on a separate row with a checkbox to the right of the row. When a user completes a task, they put a check in the checkbox, which then marks the task as complete and grays out the row. All works well with the first check box, however, when I copy/paste the checkbox down, it keeps the same cell reference so when the user checks the first box, it marks all tasks as complete because all check boxes get checked.

Is there any way (other than manually changing each cell reference) to get the check box to automatically reference the correct cell A5, A6, A7, etc. when I copy it down or is there a different way of copying so this works? I would rather not have to do this manually since some jobs have over 100 tasks.

 

[Snakehips]

mab1284,

Perhaps, try this.....

***I am assumng activex checkboxes??

For a given sheet, create your checkboxes so that cb1 is at your first row (row 5) then copy or place next in row 6 etc.

Then, run this macro to set the Linked Cells.

Sub Linkcells()
Dim shp As Shape
r = 5
For Each shp In ActiveSheet.Shapes
If shp.Type = 12 Then    ' ***type 12 is an Activex checkbox.   edit to 8  if using a form checkbox
r = r + 1
shp.OLEFormat.Object.LinkedCell = "A" & r
End If
Next shp
End Sub

Test it first!!!!

Hope that helps.

 

[Writemrsc]

Very helpful. I know this is an old post but I do have additional questions.