Hi,

I have the following situation in Excel:
I have a worksheet called "Begroting Calc" with comboboxes to select an option from a list on another sheet called "Rekenblad uitgangspunten Calc". I have created a macro which makes a copy of both sheets and renames these sheets to "Begroting WVB" and "Rekenblad uitgangspunten WVB". It also copies the comboboxes and formulas on these sheets.
The problems are:
1) I have 224 comboboxes and by inserting a row in worksheet "Begroting Calc" (the copied worksheet) it will be 225. So the amount of comboboxes can change. The macro will have to search for all comboboxes in the worksheet "Begroting WVB" and change the LinkedCell-properties from "'Rekenblad uitgangspunten Calc'!D..." to "'Rekenblad uitgangspunten WVB'!D..." and the ListFillRange-properties from "'Rekenblad uitgangspunten Calc!C...:C..." to "'Rekenblad uitgangspunten WVB'!C...:C...". I tried to create a macro myself, but it will only change the named comboboxes and uses a lot of space (see below).
2) In cells M12 to M224 of worksheet "Begroting Calc" I have these formulas:
='Rekenblad uitgangspunten Calc'!F3
='Rekenblad uitgangspunten Calc'!F6
='Rekenblad uitgangspunten Calc'!F9
and so on...
These formulas will also have to change to:
='Rekenblad uitgangspunten WVB'!F3
='Rekenblad uitgangspunten WVB'!F6
='Rekenblad uitgangspunten WVB'!F9
and so on...
But by inserting a row in "Begroting Calc" (the copied worksheet) the range will be M12:M225. So the macro will have to search for all cells in worksheet "Begroting WVB" with the formula in it.
Can someone help me with these problems? Thanks in advance!


Sub ChangeComboBoxProperties()

Dim ComboBox1 As OLEObject
Dim ComboBox2 As OLEObject
Dim ComboBox3 As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

Set ComboBox1 = ws.OLEObjects("ComboBox1")
With ComboBox1
.LinkedCell = "'Rekenblad uitgangspunten WVB'!D3"
.ListFillRange = "'Rekenblad uitgangspunten WVB'!C3:C5"
End With

Set ComboBox2 = ws.OLEObjects("ComboBox2")
With ComboBox2
.LinkedCell = "'Rekenblad uitgangspunten WVB'!D6"
.ListFillRange = "'Rekenblad uitgangspunten WVB'!C6:C8"
End With