Linkedcell Problems...

[cornishbloke]

Hi everyone,

I'm trying to copy a range of cells and activex comboboxes positioned
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 new
value required on the Quote Sheet
3) copies the comboboxes and places them over the top of their
'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 can
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 Sub

 

[BrianB]

Experience shows what pain this sort of attempt can cause.

I would nowadays :-

1. Set up a partial sheet that can be used for template and other
sheets by *adding* other bits.
2. Copy/amend the template sheet.
3. Try a simple Copy/Paste from one sheet to the other.


On of the main programming principles is

"Do as much work before run-time as possible".

This means setting things up so that the need to run code is minimised.
This can save huge amounts of time taken to run the application.

 

[cornishbloke]

Hi Brian,

Thanks for your suggestions. Unfortunately I need to be able to copy
these cells and the activex comboboxes at various times during the use
of the spreadsheet and so definitely need to automate this through
code.

I think I may be going wrong with the use of the offset function but
I'm not sure.

 

[cornishbloke]

Please.....can anybody else offer any suggestions as to what I'm doing
wrong (see original post above), I've tried numerous variations but I
can't get the code to set the linkedcell reference