Can cell links within Excel's Controls be relative

[shiver24]

Hello-

I have a spreadsheet that I've used check boxes, radio buttons, and combo boxes to assist the user in entering information into this form.

I've also created a macro that performs some copying and inserting of the lines within the form. My controls all get copied to the correct line, but the cell link is always the same as the original control that I copied.

I've tried making the cell link a relative value and that doesn't seem to work.

I want to be able to copy the control as I would any other formula and paste it into another cell and have the references update. So if the control is in cell "A1", and the cell link is "A3" and I copy the control to "B1", then the cell link should change to "B3".

Does anybody have any suggestions?

Feel free to post if you don't understand what I'm trying to explain.

Thanks,

shiver24

 

[RobBroekhuis]

The controls, as far as I know, are not linked to any one cell, and therefore cannot have relative references. The shape objects (assuming these are controls from the "Forms" toolbar) do have a "topleftcell" property which provides a range reference to their location, so that you should be able to put something together programmatically to accomplish what you're trying to do. It won't be automatic, though.
Rob

 

[shiver24]

Thanks for the information on the "topleftcell", can you explain how I can change the control using this property?

I've figureed out how to use the "topleftcell" property, but I'm unable to change the control that sits within the returned value for that range.

Thanks again!!

Shiver24

 

[RobBroekhuis]

You can find a particular control by looping through the shapes on your worksheet, for example:

Dim shp As Shape, MyCell as range
set MyCell=range("A5&quot
For Each shp In ActiveSheet.Shapes
if shp.TopLeftCell.Address=mycell.address then exit for
Next shp

Now that you have found the shape, you can manipulate its properties.
In the macro code associated with the shape you can also use the topleftcell property:

Sub button1_click()
dim MyCell as range
set MyCell=range(ActiveSheet.Shapes(Application.Caller).TopLeftCell
MyCell.offset(1,0).formulaR1C1="=R[-1]C[-1]"
End Sub

This silly example will fill the cell immediately below the top left cell covered by your button with a formula pointing to the cell to the left of your button. Play with these examples, and see if you can use the concepts to do what you're trying to accomplish.
Rob

 

[shiver24]

Rob-

Thanks again... I got it to work. You were right, this will take a bit of programming on my part.

I had to use the Microsoft KB site for a little more background, but your examples gave me what I needed.

Thanks again!!

Shiver24