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]

Welcome to MrExcel.

You can use a dynamic name for the ListFillRange so that it updates automatically when you add data:

http://www.contextures.com/xlNames01.html#Dynamic

Where are your ComboBoxes from - Form Controls or ActiveX Controls? To provide you with VBA code I would need the name of the Name you created.

 

[Skillie]

Hi Andrew

thanks for your reply they are active X. they are all named ComboBox1 to ComboBox423. the list i'm choosing from is called "food"

Kind regards

Eddie

 

[Andrew Poulsom]

Try:

Sub Combos()
    Dim i As Long
    With Worksheets("Sheet1")
        For i = 1 To 423
            .OLEObjects("ComboBox" & i).ListFillRange = "food"
        Next i
    End With
End Sub

 

[Skillie]

I've created a dynamic named range for the food list now, thank you. ready for the next step to update the ListFillRange in all combos!!

I think I will still need to know how to change other parameters from time to time as I tinker with this body of work to get its usability right.

 

[Skillie]

Fantastic!!! that worked! thank you so much you've saved me hours of effort

Try:

Sub Combos()
    Dim i As Long
    With Worksheets("Sheet1")
        For i = 1 To 423
            .OLEObjects("ComboBox" & i).ListFillRange = "food"
        Next i
    End With
End Sub

 

[Andrew Poulsom]

Try my code in Post #4.

 

[Skillie]

now for example if i wanted to edit the list row length could i use something like this still?

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

thanks again

 

[Skillie]

Try my code in Post #4.

sorry little crossover. worked a charm! my days of programming in college are long behind me thanks again

 

[Skillie]

will similar code work to edit another field like the number of rows the list displays? thanks