Change properties of a combobox with VBA

[leonidas]

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

Set ComboBox3 = ws.OLEObjects("ComboBox3")
With ComboBox3
LinkedCell = "'Rekenblad uitgangspunten WVB'!D9"
ListFillRange = "'Rekenblad uitgangspunten WVB'!C9:C11"
End With

End Sub