Drop down box for data input 1

[DaveBreder]

I need to develop a Excel macro in a data input form that when you click on the cell, a drop down box appears with valid entries. User would select entry which would appear in the form. I would need several such drop down boxes per form.

Looking for direction or other resource.

 

[Darrylles]

Hi,

Try MIS/IT forum: Microsoft: Office.

Kind Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[TomInMB]

Dave...

You need to create a sub for the initialization of the userform that will fill the list of combo boxes at the start. Example of filling the combo box full of all the countries in the world is as follows:

Private Sub UserForm_Initialize()
Dim vaCountry As Variant

vaCountry = VBA.Array("United States", "APO/FPO", "Canada", "United Kingdom", "Afghanistan", "Albania", "Algeria", "American Samoa", "Andorra", "Angola", "Anguilla", "Antigua and Barbuda", _
"Argentina", "Armenia", "Aruba", "Australia", "Austria", "Azerbaijan Republic", "Bahamas", "Bahrain", "Bangladesh", "Barbados", "Belarus", "Belgium", "Belize", "Benin", "Bermuda", _
"Bhutan", "Bolivia", "Bosnia and Herzegovina", "Botswana", "Brazil", "British Virgin Islands", "Brunei Darussalam", "Bulgaria", "Burkina Faso", "Burma", "Burundi", "Cambodia", "Cameroon", _
"Canada", "Cape Verde Islands", "Cayman Islands", "Central African Republic", "Chad", "Chile", "China", "Colombia", "Comoros", "Congo, Democratic Republic of the", "Congo, Republic of the", _
"Cook Islands", "Costa Rica", "Cote d Ivoire (Ivory Coast)", "Croatia, Republic of", "Cyprus", "Czech Republic", "Denmark", "Djibouti", "Dominica", "Dominican Republic", "Ecuador", "Egypt", _
"El Salvador", "Equatorial Guinea", "Eritrea", "Estonia", "Ethiopia", "Falkland Islands (Islas Malvinas)", "Fiji", "Finland", "France", "French Guiana", "French Polynesia", "Gabon Republic", "Gambia", _
"Georgia", "Germany", "Ghana", "Gibraltar", "Greece", "Greenland", "Grenada", "Guadeloupe", "Guam", "Guatemala", "Guernsey", "Guinea", "Guinea-Bissau", "Guyana", "Haiti", "Honduras", "Hong Kong", _
"Hungary", "Iceland", "India", "Indonesia", "Ireland", "Israel", "Italy", "Jamaica", "Jan Mayen", "Japan", "Jersey", "Jordan", "Kazakhstan", "Kenya Coast Republic", "Kiribati", "Korea, South", _
"Kuwait", "Kyrgyzstan", "Laos", "Latvia", "Lebanon", "Liechtenstein", "Lithuania", "Luxembourg", "Macau", "Macedonia", "Madagascar", "Malawi", "Malaysia", "Maldives", "Mali", "Malta", "Marshall Islands", _
"Martinique", "Mauritania", "Mauritius", "Mayotte", "Mexico", "Micronesia", "Moldova", "Monaco", "Mongolia", "Montserrat", "Morocco", "Mozambique", "Namibia", "Nauru", "Nepal", "Netherlands", _
"Netherlands Antilles", "New Caledonia", "New Zealand", "Nicaragua", "Niger", "Nigeria", "Niue", "Norway", "Oman", "Pakistan", "Palau", "Panama", "Papua New Guinea", "Paraguay", "Peru", "Philippines", _
"Poland", "Portugal", "Puerto Rico", "Qatar", "Romania", "Russian Federation", "Rwanda", "Saint Helena", "Saint Kitts-Nevis", "Saint Lucia", "Saint Pierre and Miquelon", "Saint Vincent and the Grenadines", _
"San Marino", "Saudi Arabia", "Senegal", "Seychelles", "Sierra Leone", "Singapore", "Slovakia", "Slovenia", "Solomon Islands", "Somalia", "South Africa", "Spain", "Sri Lanka", "Suriname", "Svalbard", _
"Swaziland", "Sweden", "Switzerland", "Syria", "Tahiti", "Taiwan", "Tajikistan", "Tanzania", "Thailand", "Togo", "Tonga", "Trinidad and Tobago", "Tunisia", "Turkey", "Turkmenistan", "Turks and Caicos Islands", _
"Tuvalu", "Uganda", "Ukraine", "United Arab Emirates", "United Kingdom", "United States", "Uruguay", "Uzbekistan", "Vanuatu", "Vatican City State", "Venezuela", "Vietnam", "Virgin Islands (U.S.)", _
"Wallis and Futuna", "Western Sahara", "Western Samoa", "Yemen", "Yugoslavia", "Zambia", "Zimbabwe&quot

cbCountry.List = vaCountry

End Sub

The userform's combo box name would be "cbCountry". You could have multiple combo boxes with differnet names and fill them all with whatever items you wanted.

Hope this helps!

 

[xlbo]

Ouch - personally - I think you just need
Data>Validation

Select the cell - goto Data>Validation
Select List
Then, either enter a list of appropriate values or reference a set of cells whichhold the data

If you want to reference a set of cells on a different sheet (recommended) you will need to NAME the range

Say you have a list of values in Sheet2!A1:A10
Goto Insert>Name>Define
Call it Data1

Then, in Data>Validation>List, enter
=Data1

Should do what you want (if you are working on a worksheet as opposed to a userform) Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[DaveBreder]

Thanks for all replies.

xlbo's solution was what I was looking for. thanks

 

[kramsey]

I have a combo box in a form which is linked to a table in my database. I want to be able to add a record to the combo box (table) if it is not in the list. Does anyone have the code to add a record on NotInList event?

 

[xlbo]

KRamsey - please start a new thread.
1: You are more likely to get a response
2: You do not confuse the thread Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes