Direct way
Const SheetName As String = "'Rekenblad uitgangspunten WVB'!"
Sub ChangeComboBoxProperties()
Call LinkCombo(ws.OLEObjects("ComboBox1"), "D3", "C3:C5")
Call LinkCombo(ws.OLEObjects("ComboBox2"), "D6", "C6:C8")
Call LinkCombo(ws.OLEObjects("ComboBox3"), "D9", "C9:C11")
End Sub
Private Sub LinkCombo(pCombo As OLEObject, pLink As String, pList As String)
With pCombo
.LinkedCell = SheetName & pLink
.ListFillRange = SheetName & pList
End With
End Sub
Sub ChangeFormula()
Sheets("Begroting WVB").Activate
Call AddFormula(Range("M12"), "F3")
Call AddFormula(Range("M13"), "F6")
Call AddFormula(Range("M14"), "F9")
End Sub
Private Sub AddFormula(pRng As Range, pCell As String)
pRng.FormulaR1C1 = SheetName & pCell
End Sub
Less obvious, but less typing/copy-pasting for you, just change the upper
limit of the loop from 3 to your top end
Const SheetName As String = "Sheet3!" '"'Rekenblad uitgangspunten WVB'!"
Sub ChangeComboBoxProperties()
Dim ws As Worksheet
Dim i As Long
Set ws = ActiveSheet
For i = 1 To 3
Call LinkCombo(ws.OLEObjects("ComboBox" & i), "D" & i * 3, "C" & i *
3 & ":C" & i * 3 + 2)
Next i
End Sub
Private Sub LinkCombo(pCombo As OLEObject, pLink As String, pList As String)
With pCombo
.LinkedCell = SheetName & pLink
.ListFillRange = SheetName & pList
End With
End Sub
Sub ChangeFormula()
Dim i As Long
Sheets("Begroting WVB").Activate
For i = 1 To 3
Call AddFormula(Range("M" & i + 11), "F" & i * 3)
Next i
End Sub
Private Sub AddFormula(pRng As Range, pCell As String)
pRng.FormulaR1C1 = SheetName & pCell
End Sub
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"leonidas" wrote in
message ...
Hi,
I have the following two macros in Excel. The only thing is they take a
lot of space and time to type, because below I have the first three
objects, but it will have to be 250 objects! So can somebody help me
rewrite these two macros so that they will be a lot shorter? 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
--------------------------------------------------------------------
Sub ChangeFormula()
Sheets("Begroting WVB").Activate
Range("M12").FormulaR1C1 = "'Rekenblad uitgangspunten WVB'!F3"
Range("M13").FormulaR1C1 = "'Rekenblad uitgangspunten WVB'!F6"
Range("M14").FormulaR1C1 = "'Rekenblad uitgangspunten WVB'!F9"
End Sub
--
leonidas
------------------------------------------------------------------------
leonidas's Profile:
http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=553945