what's wrong with my code????

[cornishbloke]

Almost ready to pull my hair out over this one...

Why can't I get the following code to assign the linkedcell value of an
activex combobox????? After running this code the linkedcell value
remains blank.

Sheets("Row Template").Activate
ActiveSheet.ComboBox1.LinkedCell = Range("Offset(insertpoint, -6, 1)")

Yet if I change the code following the '=' symbol to a direct reference
(as below) it works:

Sheets("Row Template").Activate
ActiveSheet.ComboBox1.LinkedCell = "A1"


Am I using the offset method incorrectly???? How do I do this? Please
please help me!

 

[Bob Phillips]

Cornish,

I haven't tried it, but 3 things look wrong. Offset is a property of the
Range object , not an argument to range, it on ly has 2 arguments (row and
column), and Range must refer to cell(s). Try something like


ActiveSheet.ComboBox1.LinkedCell = Range("G1").Offset(insertpoint, -6)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[cornishbloke]

Thanks Bob,

I tried your suggestion, adapting it as follows, but when i checked th
combobox the value for the linkedcell was blank.

ActiveSheet.ComboBox1.LinkedCell = Range("insertpoint").Offset(-6, 1)

insertpoint is the named range that I'm trying to offset from to ge
the linkedcell reference.

Am I missing something

 

[Bob Phillips]

We were missing something on the command. It should be

ActiveSheet.ComboBox1.LinkedCell = Range("insertpoint").Offset(-6,
1).Address

as the linked cell is not a range, but a range string.

The other thing to be aware of is that as soon as you set the linked cell,
the combobox is updated with is value (empty?), so it may be best to load it
before setting linkedcell

With ActiveSheet.ComboBox1
Range("insertpoint").Offset(-6, 1).Value = .Value
.LinkedCell = Range("insertpoint").Offset(-6, 1).Address
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[cornishbloke]

Bob,

Once again I owe you my gratitude, thank you very much for your help -
that answers a problem I've had for days and it works perfectly.

 

[Bob Phillips]

As I say, it's a pleasure to help a fellow south-west man.

Regards

Bob