Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
LinkedCell problem
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
LinkedCell problem
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 "TFriis" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
LinkedCell problem
On 27 Nov., 13:04, Mike H wrote:
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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
LinkedCell problem
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
LinkedCell problem
On 27 Nov., 13:23, TFriis wrote:
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
setting LinkedCell using VBA | Excel Programming | |||
Export Linkedcell | Excel Programming | |||
Linkedcell protection problem | New Users to Excel | |||
LinkedCell Update does not always respond | Excel Programming | |||
Linkedcell Problems... | Excel Programming |