Adding items to a combobox

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,057
Office Version
  1. 2016
Platform
  1. Windows
I have a userform , with multi page on it, on some of the pages I have combo boxes. I used the following bit of the code to add items to the combo-boxes. I used the same code for each of combo-boxes. I just changed the NAME so it referred to the correct combo-box

VBA Code:
Private Sub ComboBox1_Enter()
ComboBox1.AddItem ("Car")
ComboBox1.AddItem ("Bus")
ComboBox1.AddItem ("Bike")
ComboBox1.AddItem ("Train")
ComboBox1.AddItem ("Walking")
End Sub

The Problem
The problem I am having is that each time I select an item, it reshows it in the dropdown of the combo-box, hence the list of dropdown grows.

In the attached example, select a option from the combo box and then click into the textbox, then open combo-box again and the list should have grown.
Download File
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,658
Office Version
  1. 2019
Platform
  1. Windows
Hi, use the the forms initialize event

VBA Code:
Private Sub UserForm_Initialize()
    Me.ComboBox1.List = Array("Car", "Bus", "Bike", "Train", "Walking")
End Sub

Dave
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Either Clear the combobox or populate using an array.
Code:
ComboBox1.List = Array("Car", "Bus", "Bike", "Train", "Walking")
 
Solution

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,057
Office Version
  1. 2016
Platform
  1. Windows
Norie

My list is long, and will go off screen, is there a way it could be done as shown below, so it is easier to read.

VBA Code:
ComboBox1.List = Array("Car", 
"Bus", 
"Bike",
"Train", 
"Walking")
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,658
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

To cut lenght of list down Just insert a continuation

VBA Code:
Me.ComboBox1.List = Array("Car", "Bus", "Bike", _
                              "Train", "Walking")

Dave
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Rather than a line for each item, put a few items on each line and use a continuation character.
VBA Code:
ComboBox1.List = Array("Car", "Bus", "Bike", _
                                        "Train", "Walking")
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,057
Office Version
  1. 2016
Platform
  1. Windows
Thanks I did try that, but it gave me a error, that is why I asked. I have tried it again and its has worked.

Thanks to everyone for there help
 

Forum statistics

Threads
1,136,274
Messages
5,674,774
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top