LinkedCell problem

[TFriis]

Hi experts.

I have a problem with a checkbox' linkedcell. - the following code
works as a charm - but if I want to change the reference to another
sheet, it doesn't work (sub test2)


'------------------- Code that works ---------------

Sub test()

Dim rng As Range
Dim myRange As Range

'On Error Resume Next

Set myRange = Range("A10")
Set rng = Range("A11")

ActiveSheet.CheckBoxes.Add(myRange.Left, myRange.Top,
myRange.Width, myRange.Height).Select
With Selection
.LinkedCell = rng(1, 1).Address
.Characters.Text = "Test"
End With

End Sub

'------------------- Code that does NOT works ---------------

Sub test2()

Dim rng As Range
Dim myRange As Range

'On Error Resume Next

Set myRange = Range("A10")
Set rng = Range("A11")

ActiveSheet.CheckBoxes.Add(myRange.Left, myRange.Top,
myRange.Width, myRange.Height).Select
With Selection
.LinkedCell = Sheets("Sheet1").rng(1, 1).Address
.Characters.Text = "Test"
End With

End Sub

Can anybody help me?

 

[Mike H]

Try this:-


Sub test2()

Dim rng As Range
Dim myRange As Range

'On Error Resume Next

Set myRange = Range("A10")
Set rng = Range("A11")

ActiveSheet.CheckBoxes.Add(myRange.Left, myRange.Top, _
myRange.Width, myRange.Height).Select

With Selection
.LinkedCell = "Sheet1!$A$1"
.Characters.Text = "Test"
End With
End Sub


Mike

 

[TFriis]

Try this:-

Sub test2()

Dim rng As Range
Dim myRange As Range

'On Error Resume Next

Set myRange = Range("A10")
Set rng = Range("A11")

ActiveSheet.CheckBoxes.Add(myRange.Left, myRange.Top, _
myRange.Width, myRange.Height).Select

With Selection
.LinkedCell = "Sheet1!$A$1"
.Characters.Text = "Test"
End With
End Sub

Mike

That works fine.. Dunno why, but I have tested this a 100 time :S

But it works now - thanks

Sub test2()

Dim rng As Range
Dim myRange As Range

'On Error Resume Next

Dim shname As String
shname = "Sheet2"

Set myRange = Range("A10")
Set rng = Range("A11")

ActiveSheet.CheckBoxes.Add(myRange.Left, myRange.Top,
myRange.Width, myRange.Height).Select
With Selection
.LinkedCell = shname & "!" & rng(1, 1).Address
.Characters.Text = "Test"
End With

End Sub

 

[TFriis]

New problem though, if the sheetname is something like:

Dim shname = "Sheet 2" '(with a space between t and 2)

Then it doesn't work again - solutions anyone?

 

[TFriis]

New problem though, if the sheetname is something like:

Dim shname = "Sheet 2" '(with a space between t and 2)

Then it doesn't work again - solutions anyone?

Ok something like this works:

..LinkedCell = "'" & shname & "'" & "!" & rng(1, 1).Address

But I don't really get it :/