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$1offset(insertpoint2,0,6))"
End Su
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$1offset(insertpoint2,0,6))"
End Su