Re-assign LinkedCell in VBA

C

cornishbloke

Hi,

I'm trying to copy a range of cells and activex comboboxes positione
over them, from one sheet ("template") to another sheet ("Quote").

The following code
1) copies the cells and their bordering/formatting etc,
2) attempts to set the linkedcell values of each combobox to the ne
value required on the Quote Sheet
3) copies the comboboxes and places them over the top of thei
'would-be' linked-cells
4) resets the print area.

I can't get the second stage, above, to work correctly.

I'm still learning VB so if you could explain any solutions you ca
provide I'd be grateful!


----------------------------------------------------------
Sub addrow()

Sheets("Template").Range("A1:G6").Copy
With Sheets("Quote").Range("insertpoint")
.Insert Shift:=xlDown
.Offset(-5, 0).Value = .Offset(-11, 0).Value + 1
End With

Sheets("Template").Activate
ActiveSheet.ComboBox1.LinkedCell = "Offset(insertpoint,-6,1)"
ActiveSheet.ComboBox2.LinkedCell = "Offset(insertpoint,-5,1)"
ActiveSheet.ComboBox3.LinkedCell = "Offset(insertpoint,-4,1)"
ActiveSheet.ComboBox4.LinkedCell = "Offset(insertpoint,-3,1)"
ActiveSheet.ComboBox5.LinkedCell = "Offset(insertpoint,-2,1)"
ActiveSheet.ComboBox6.LinkedCell = "Offset(insertpoint,-1,1)"

ActiveSheet.Shapes.Range(Array("ComboBox1, ComboBox2, ComboBox3
ComboBox4, ComboBox5, ComboBox6")).Select
Selection.Copy
Sheets("Quote").Select
Range("Offset(insertpoint, -6, 1)").Select
ActiveSheet.Paste


Sheets("Quote").PageSetup.PrintArea
"$A$1:(offset(insertpoint2,0,6))"
End Su
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top