Displaying all the states in a VBA combobox

  • Thread starter Thread starter Todd Huttenstine
  • Start date Start date
T

Todd Huttenstine

Hey

Is there a special code that will display all the states
in the USA in a combobox or do I have to do this manually?


Thanx

Todd Huttenstine
 
Here is some code I posted back in 2001

Sub AddState()

aStates = Array _
("Alabama", "Alaska", "Arizona", "Arkansas", "California", _
"Colorado", "Connecticut", "Delaware", "Florida", _
"Georgia", "Hawaii", "Idaho", "Illinois", "Indiana", _
"Iowa", "Kansas", "Kentucky", "Louisiana", "Maine", _
"Maryland", "Massachusetts", "Michigan", "Minnesota", _
"Mississippi", "Missouri", "Montana", "Nebraska", _
"Nevada", "New Hampshire", "New Jersey", "New Mexico", _
"New York", "North Carolina", "North Dakota", "Ohio", _
"Oklahoma", "Oregon", "Pennsylvania", "Rhode Island", _
"South Carolina", "South Dakota", "Tennessee", "Texas", _
"Utah", "Vermont", "Virginia", "Washington", _
"West Virginia", "Wisconsin", "Wyoming")

For Each cell In Range("A1:A50")
cell.Offset(0, 1).Value = aStates(cell.Value - 1)
Next
End Sub

change it to

Sub AddState()
Dim aStates as Variant
Dim i as Long
aStates = Array _
("Alabama", "Alaska", "Arizona", "Arkansas", "California", _
"Colorado", "Connecticut", "Delaware", "Florida", _
"Georgia", "Hawaii", "Idaho", "Illinois", "Indiana", _
"Iowa", "Kansas", "Kentucky", "Louisiana", "Maine", _
"Maryland", "Massachusetts", "Michigan", "Minnesota", _
"Mississippi", "Missouri", "Montana", "Nebraska", _
"Nevada", "New Hampshire", "New Jersey", "New Mexico", _
"New York", "North Carolina", "North Dakota", "Ohio", _
"Oklahoma", "Oregon", "Pennsylvania", "Rhode Island", _
"South Carolina", "South Dakota", "Tennessee", "Texas", _
"Utah", "Vermont", "Virginia", "Washington", _
"West Virginia", "Wisconsin", "Wyoming")

For i = lbound(aStates) to ubound(aStates)
Userform1.combobox1.AddItem aStates(i)
Next

'or just
' Userform1.combobox1.List = aStates

End Sub
 
Back
Top