Excel/VBA - Populate ComboBox with Values Solved

Question

Hello everyone,

On my Excel 2003 sheet, I have a ComboBox (created via Design mode, not a dropdown list created by Data/Validation). I would like that when I open my Excel sheet, it "loads" with the values from a range of cells (for example, range (Q1:Q4)).
I can't seem to do this; I tried (in VBA):

Private Sub WorkBook_Open() ComboBox1.RowSource = Sheets("Feuil1").Range("Q1:Q4").Value End sub

but it doesn't work.. I tried removing the "", removing the .Value, I messed with this syntax without being able to achieve my goal. Following a suggestion on another forum, I tried this:

Private Sub WorkBook_Open() ComboBox1.List = Sheets("Déperditions").Range("Q1:Q4").Value End Sub

But it doesn't work; it gives me the error "Object Required". Does anyone have an idea to solve this problem?

Thank you in advance for your responses,

Best regards

RussellD · Accepted Answer

Hello pijaku,

thank you very much, your tip works perfectly. However, I have a new problem: the values are correctly stored in the ComboBox, but when I click on it, they do not display in it... Is it related to the event used?

pijaku · Answer

Hello,
Try to make your combobox work with the DropButtonClick() event. The code should look like this:

Private Sub ComboBox1_DropButtonClick() ComboBox1.Clear Dim i As Integer With Sheets("Déperditions") For i = 1 To 4 ComboBox1.AddItem .Cells(i, 17) Next i End With End Sub

Regards,
-- Every problem has its solution. If there is no solution, where is the problem? --

pijaku · Answer

I don't really know what all the events consist of... I'm testing and looking at the one that works. I naively thought that changing the event might work. As a result, I misled you. You were on the right track. It is indeed necessary to combine what you did (your event was good: WorkBooks_Open) and my code:

Private Sub Workbook_Open() Sheets("Feuil1").ComboBox1.Clear Dim i As Integer For i = 1 To 6 Sheets("Feuil1").ComboBox1.AddItem Sheets("Feuil1").Cells(i, 17) Next i End Sub

RussellD · Answer

Re,

I had the same idea as you, I tried the code you just wrote. But it doesn't work, and it keeps giving me a "Required Object" error, referring to ComboBox1. It's strange because your code worked very well with the DropButtonClick event (aside from the small issue we discussed...)
Basically, it's as if when the Excel file opens, it doesn't detect the presence of my combobox, which is odd...
I will keep searching, anyway thank you very much for your help.
If I find a solution, I will of course share it.

Best regards.

pijaku · Answer

It works well for me, check the name of your sheets in :

Private Sub Workbook_Open() Sheets("Sheet1").ComboBox1.Clear Dim i As Integer For i = 1 To 6 Sheets("Sheet1").ComboBox1.AddItem Sheets("Sheet1").Cells(i, 17) Next i End Sub

RussellD · Answer

: - ) It works perfectly!
Indeed, I hadn't specified the sheet where my combobox was located, that's why it couldn't find it. Everything is sorted out now.
Anyway, thank you very much for your help, pijaku. See you soon!

Best regards

Excel/VBA - Populate ComboBox with Values

6 réponses

Texture issues in enshrouded

Old lullaby

Unable to connect to freebox os

Edit ad on leboncoin for free

Freebox wifi password

Computer tower that won't turn on

Blocked: are my messages still being delivered?

Video intercom inspector

Your computer can't use another display.

Fransat decoder stuck