Mass Check Box VBA with specific Sheet Name reference

[sammygirl]

Hi Guys,

This below macro works great - HOWEVER how would I assign it so that it is worksheet specific??

Ie when you select a check box it is linked to "sheet1"$b$9 not just $B$9.

I can't seem to make it work....

Hope that makes sense!
Cheers
Sammy

Sub AddCheckBoxes()
  On Error Resume Next
  Dim c As Range, myRange As Range
  Set myRange = Selection
  For Each c In myRange.Cells
  ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select
  With Selection
  .LinkedCell = c.Address
  .Characters.Text = ""
  .Name = c.Address

  End With
  Next
  myRange.Select
 End Sub

 

[Hui]

Change the .Linkedcell line to:

 .LinkedCell = "'" + ActiveSheet.Name + "'" + "!" + c.Address

 

[NARAYANK991]

Hi Sammy ,

I don't understand why you want that the checkbox should have the sheet name in the CellLink address ; after all , the checkbox is physically located on a sheet ; if the CellLink address says $A$1 alone , it clearly means cell A1 on the sheet in which the checkbox is located.

What purpose would be served if the sheet name were included , unless you want a different sheet name ?

See the following :

Sub AddCheckBoxes()
    Const DIFFERENTSHEETNAME = "Sheet4"
    Dim c As Range, myRange As Range
    Set myRange = Selection
    myRange.Parent.Activate
   
    For Each c In myRange.Cells
        ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select
        With Selection
            .LinkedCell = DIFFERENTSHEETNAME & "!" & c.Address
            .Characters.Text = ""
            .Name = c.Address
        End With
    Next
    myRange.Select
End Sub

Narayan

 

[sammygirl]

Hui! You're a genius thank you

Narayan - I need to copy the specific checkboxes onto another sheet but with the same cell link. It's basically a to do list with a dashboard connection.

Cheers all