Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Use an Array to populate several ComboBoxes

0

I have several ComboBoxes on a Sheet called ComboBox1, ComboBox2, ComboBox3, etc. I want to populate each ComboBox Value Property using an array of Data. e.g. ComboBox & i = Array(1,i), where i is the counter in a For Next Loop.

I cannot work out how to increment each ComboBox Name to get the result I want. Please help.

Here is my code:

Private Sub DisplayRecords()

Dim row As Long, i As Integer
row = wsControls.Range("D3").Value

' Populate Database Array with selected Function
Dim details As Variant
details = db.Range("A" & row + 1 & ":" & "CK" & row + 1)

' Populate Input Form with Selected Function
With wsControls
For i = 1 To 20
.ComboBox & i.Value = details(1, i) ' THIS IS WHERE THE PROBLEM IS!!!
Next i
End With

End Sub

Answer
Discuss

Answers

0
Selected Answer

Hi and welcome to the Forum.

If your worksheet has ActiveX ComboBoxes, please replace your line:

.ComboBox & i.Value = details(1, i) ' THIS IS WHERE THE PROBLEM IS!!!

with:

.Shapes("ComboBox" & i).OLEFormat.Object.Object.Value = details(1, i)
 

Hope this fixes your problem. If so, please remember to mark this answer as Selected.

For future reference, you should use the CODE button to enclose your code for copying (as I have above). Also  it usually helps us (saves time) if you attach a file to your Question- use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing macro and data.

Discuss

Discussion

Thanks John, that did the trick. I had 89 Control boxes so the code looks a lot better than than having 89 lines and it runs faster now too.
tonyimbruglia (rep: 6) Dec 10, '22 at 6:55 pm
Thanks for selecting my answer, Tony. 

I just corrected my answer so it reads:
 
.Shapes("ComboBox" & i).OLEFormat.Object.Object.Value = details(1, i)

not "&1" but I guess you've already spotted my error (from my test code) since you have running code. 
John_Ru (rep: 6142) Dec 11, '22 at 2:35 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login