Filling combo box with VBA 2

[joesofg]

I have a combo box I wish to fill with item names using only VBA ie., no copying an array from somewhere else. Is this possible?

I have tried the following without success:

Private Sub Forms.ComboBox1_Change()
With ComboBox1
.ListFillRange = ""
'.AddItem "A"
'.AddItem "B"
'.AddItem "C"
'.AddItem "D"
'.AddItem "E"
'.AddItem "F"
End With

End Sub

This is used in Excel 2003 without success. Would appreciate any assistance.

 

[johnwm]

ListFillRange property returns or sets the worksheet range used to fill the specified list box. You have set the range to "" so the list will be empty. If you want to fill from a range use:
.ListFillRange = "A18:A24"

To fill with literals use the AddItem method
.Clear
.AddItem "A"
.AddItem "B"

Note that at the moment your code is in it's own change event, which will lead to odd results

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[joesofg]

Thanks for this information, John.

I really just want to fill the drop down menu using the AddItem method, but it still does not work for me, even using your suggested code.

As I am new to VBA, can you please explain the meaning of your comment below, and how should I get around it:

"Note that at the moment your code is in it's own change event, which will lead to odd results"

My regards,
Joe

 

[HarleyQuinn]

If you try putting a command button on your worksheet and taking the code John provided out of the Combobox_Change event and put it in the Click event of the Command Button. That should show you the results you desire.

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[joesofg]

Thanks for the suggestions, but not sure I need another command button for users who will not understand the purpose of this.
My regards,
Joe

 

[HarleyQuinn]

The command button suggestion was for you, to show you that the code provided by John worked correctly. You can populate the combobox whenever you want really but the event you currently have it on will probably (as pointed out by John) not give the behaviour you are wanting.

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[joesofg]

Thanks for your assistance, Harley, it is very much appreciated. The code below almost works.

Can you please tell me why it will not work after the spreadsheet is shut down and restarted? It retains the selected item of the run done before the shutdown, but has nothing else in the drop down menu. It loads again after I shift to another worksheet and back.

I have removed the .Clear statement but same thing happens.

Private Sub Worksheet_Activate()
With ComboBox1
.Clear
.ListFillRange = ""
.AddItem "Test1"
.AddItem "Test2"
.AddItem "Test3"
End With
End Sub

any suggestions greatly appreciated.

My regards,
Joe

 

[HarleyQuinn]

Hi Joe,

In the ThisWorkbook (rather than in the specific Worksheetss code) section of the code you could try the following code. It has just worked for me:

Private Sub Workbook_Open()
With Sheet1.ComboBox1
    .Clear
    .ListFillRange = ""
    .AddItem "Test1"
    .AddItem "Test2"
    .AddItem "Test3"
End With
End Sub

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[joesofg]

Excellent. Many thanks Harley - this had me stumped for days. It works perfectly.

I have learnt lots from you guys on this little exercise.

My regards,
Joe

 

[alonzo51087]

i'm new to VBA also and I'm having a problem with adding information to a combo box. I tried using

Private Sub Workbook_Open()
With Sheet1.ComboBox1
.Clear
.ListFillRange = ""
.AddItem "A"
.AddItem "B"
.AddItem "C"
End With
End Sub

, but it's not working. Could someone please help!

I also have to copy the information from the VBA-based form back into an Excel worksheet. Any ideas?

 

[johnwm]

It seems to work for me, but until you select an item in the combo, nothing will show. Try adding:

.ListIndex = 0

after .AddItem "C"

If you want the same info in a spreadsheet as in the combobox, then do it the other way round!

Put your values in the spreadsheet and use the ListFillRange property as explained in my first reply above

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[alonzo51087]

Thank you John!

I used your code, but I couldn't get it to work. Is there any way to add the Combo Box values inside of VBA (Forms>UserForm1) and input the chosen value from the form into the Excel Worksheet?

 

[johnwm]

If you want this to happen in a UserForm, add a combox to your userform and put the following code on the UserForm code page:

Private Sub UserForm_Activate()
With ComboBox1  'this loads the combo
    .AddItem "x"
    .AddItem "y"
    .AddItem "z"
End With
End Sub

Private Sub ComboBox1_Change() 'this writes the selected value to the worksheet
Sheets("sheet1").Range("C1") = ComboBox1.Value
End Sub

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller