Editing multiple Combo Boxes at once

[Skillie]

Hi guys I’m new to the forum. I have average excel experience and know nothing about VB J I’m using Excel 2007.

Here is the issue I want to resolve.

I’ve recently exceeded my abilities by adding approx 400 combo boxes to a sheet; lets call it “sheet1”; manually by copying and pasting the first one I created. Data Validation lists was no use as the text size was too small and only displays 8 lines at a time which made the list awkward to use.

I’ve set the ListFillRange to Sheet2!A2:A160.

I now need to change that range to Sheet2!A2:A300 as the list of data is growing and I foolishly didn’t think of that scenario! I’ve already suffered the agony of manually editing the 500 comboboxes linked cell parameter manually and don’t want to have to do the same every time I wish to change a parameter. The combo boxes are choosing from a list of foods and pulling in their macro nutrient data to a meal menu, 6 meals a day, 10 lines per meal, 7 days which is why I ended up with approx 400+ boxes.

Can anyone advise me of a piece of code and maybe how to run it as I’ve never used VB in excel. I’m sure it’s very simple for all you experts out there! J

Hope that makes sense and thanks for any advice in advance.

 

[Andrew Poulsom]

It would be:

.OLEObjects("ComboBox" & i).Object.ListRows = 20

 

[Skillie]

It would be:

.OLEObjects("ComboBox" & i).Object.ListRows = 20

compile error
ambiguos name detected: Combos

 

[Andrew Poulsom]

You can't have more than one procedure with the same name in a module. Give it a different name.

 

[Skillie]

You can't have more than one procedure with the same name in a module. Give it a different name.

thank you sir this worked perfectly. appreciate the spoon feeding for a VB novice

Sub Comborow()
Dim i As Long
With Worksheets("Menu week 1")
For i = 1 To 423
.OLEObjects("ComboBox" & i).Object.ListRows = 20
Next i
End With
End Sub