LinkedCell problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TFriis - 27 Nov 2007 11:42 GMT

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("A1:D1")

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("A1:D1")

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?

Reply to this Message

Mike H - 27 Nov 2007 12:04 GMT

Try this:-

Sub test2()

Dim rng As Range
Dim myRange As Range

'On Error Resume Next

Set myRange = Range("A10")
Set rng = Range("A1:D1")

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

> Hi experts.
>
[quoted text clipped - 45 lines]
>
> Can anybody help me?

Reply to this Message

TFriis - 27 Nov 2007 12:15 GMT

> Try this:-
>
[quoted text clipped - 18 lines]
>
> 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("A1:D1")

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

Reply to this Message

TFriis - 27 Nov 2007 12:23 GMT

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?

Reply to this Message

TFriis - 27 Nov 2007 12:30 GMT

> 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 :/

Reply to this Message