Excel Checkbox code issue 2

[elohelae]

Hello

I used the code below to enter multiple checkboxes in an excel 2010 sheet.

I need to alter it to link to cells with the same reference but in a different sheet and I cannot get it to work. The name of the sheet is MyCalc

Please can you help? The code is below and any help would be appreciated.

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
c.Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & c.Address & "=TRUE"
.FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked
.FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked
.Font.ColorIndex = 2 'cell background color = White
End With
Next
myRange.Select
End Sub


The code was from the following website

http://www.extendoffice.com/documents/excel/901-excel-insert-multiple-checkboxes.html

 

[SkipVought]

hi,

Did you activate sheet MyCalc and select the appropriate cells?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[elohelae]

Skip

Thank you for your response.

The code works correctly when the cells are selected, however it stores the true / false value in the same cells as the checkbox. My goal is to save the true / false value to a linked cell in another sheet, that way I can process the information and go from there.

I appreciate you looking at this post and if there is anymore information that you require then please let me know.

cheers

 

[SkipVought]

What other sheet?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[elohelae]

Skip

I have a sheet which I want to place the checkboxes in, however I want the true / false recording of this data in another sheet called MyCalc in the same workbook.

I used the above code to correctly link the checkbox to the cell in which the checkbox is displayed, however I need this link to go to a cell on another sheet


Currently if I select cell B1 and run the code, then the linked call for the checkbox is cell B1, however I need the linked value to be MyCalc!B1 and do it using the code above.
Does this make sense?

cheers

 

[SkipVought]

How 'bout this...

Sub AddCheckBoxes()
    On Error Resume Next
    Dim c As Range, myRange As Range, i As Integer
    Set myRange = Selection
    For Each c In myRange.Cells
        
        With ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height)
            i = ActiveSheet.Shapes.Count
            .LinkedCell = "MyCalc!" & c.Address
            .Characters.Text = "Some Name" & i
        End With
        With Sheets("MyCalc").Range(c.Address)
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, _
                Formula1:="=" & .Address & "=TRUE"
            .FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked
            .FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked
            .Font.ColorIndex = 2 'cell background color = White
        End With
    Next
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[elohelae]

Skip

perfect, thank you for your patience and your help with this.

cheers