[OTP] VBA Using combobox in Excel
Hi there,
I'm working on an engineering spreadsheet (Excel) and need some help in manipulating comboboxes / dropdown list.
Say i have 2 list, where list A has the list of countries and list B has the list of cities for the country chose. If all the data source (i.e. countries and cities) are to be extracted from the Excel spreadsheet, is it possible to make list B dependant on list A (that is to say when user selects a country in list A, list B will automatically gives the respective cities) WITHOUT creating a userform? If so, please advise how.
Hope you can understand what i'm trying to ask here. Fyi i have minimum VB knowledge so a "dummyproof" explaination would be really nice. Thanks :)
Oh btw i'm using VB in MS Excel 2003, if it matters...
[1453 byte] By [
gidyeo] at [2007-12-22]
Hello,
Yes this will be possible but please explain further, you say you want comboBoxes filled with this info but don't wnat to use a userform, do you want a combobox embedded in the spreadsheet?
Also suppose column A has a list of countries, where would the list of the cities within that country be. You must be relating the cities to the country somewhere.
If I was to select country "Argentina", where would you look up the cities that are in "Argentina". If you have a list of cities then how do you determine which city belongs to which country?
A screenshot of your Countries and Cities data would help solve the problem.
ChasAA
Yes, i would like to have 2 comboboxes embedded in the spreadsheet.
Say column A has the list of countries, and when user selects the
desired country from combobox1, the list of respective cities will then
populate combobox2 for user to choose from. The list of cities, if
possible, should be read from the data from the tabs that are named
after the countries.
I know this sounds stupid cuz in this way there will be too many tabs
to have in the spreadsheet. However this is just an illustration of
what is required in the engineering spreadsheet that i'm doing.
So say when user select "Argentina", the program should then read the list of cities from tab "Argentina" (say column A)...
Hope this is clearer now... :)
Hello,
Here is one approach, Have two work sheets, "Countries" and the other "Main". You could have one but I chose to have two so that the user does not see all the country and city data!.
Sheet "Countries" contains all the country and city data.
From Cell A1 downwards will be the list of countries.
The cities start from A2 across (I didn't think it would not be a good idea to have a sheet for each country). So will look like this:
Argentina ArgCity1 ArgCity2 ArgCity3 etc
Brazil BraCity1 BraCity2 etc
On sheet "main" embed two comboboxes ie combobox1 and combobox2.
For combobox1 set the "ListFillRange" to the range area covered by countries ie "countries!A1:A196". (If you decide to use only one sheet you do not need to include "countries!" and change the code accordingly.
To set the ListFillRange, when in "design" mode, right click the combobox1 nd select properties and you will see all the properties for that combobox.
Still indesign mode, right click on combobox1 and select view code. A coding screen will appear and type the following:
[Code Start]
Sub ComboBox1_Change()
Dim off As Integer
Dim thisCell As String
Dim thisRow As Integer
Application.ScreenUpdating = False ' turn off screenupdating so you dont see screen flicker
Worksheets("countries").Select ' select the sheet with list of countries and cities
thisRow = Worksheets("main").ComboBox1.ListIndex ' Country list must start at row 1 for this to work
Worksheets("countries").Cells(thisRow + 1, 2).Select ' select the cell that corresponds with listindex+1 ' (listindex 0 is row 1, listindex 1 is row 2 etc)
Worksheets("main").ComboBox2.Clear ' Clear all list items from combobox2
off = 0 ' initialise offset
thisCell = ActiveCell.Offset(0, off).Value ' read current city
Do While thisCell <> "" ' while the city is not blank
Worksheets("main").ComboBox2.AddItem (thisCell) ' add this city to combobox2's list
off = off + 1 ' set next cells offset
thisCell = Selection.Offset(0, off).Value ' add this city to combobox2's list
Loop ' repeat process
Worksheets("Main").Select ' select the sheets with comboboxes again
End Sub
[Code Ends]
Good Luck,
Any problems please write for my email address, and yours and I'll send you the sheet I created
Chas
The post did not format as I was seeing it on my screen. So please remember the text in blue and red is only remarks. Do no enter them as code. (the line has overlapped onto the next and now looks like part of the code!
Chas
I managed to follow almost every step you mentioned, however i've got the following error message when running the code.
Run-time error '438':
Object doesn't support this property or method
with the following line highlighted in yellow:
thisRow = Worksheets("main").ComboBox1.ListIndex ' Country list must start at row 1 for this to work
Any idea what's wrong?
gidyeo
btw i didn't manage to "view code" by right clicking on the combobox simply because there isn't such option to choose from...
Instead i've added the code by clicking the "edit code" button (from "Forms" toolbar) while having the combobox selected.
And one more thing is that my default code started with:
Sub DropDown2_Change ()
Instead of the mentioned Sub ComboBox1_Change() .... does this makes any difference?
gidyeo
Erm btw i've "embedded" the combobox by clicking Combobox button from the Form toolbar, is that the reason why the code is not functioning?
I think the code didn't recognise my "combobox" as a combobox...
Yes, more than likely the highlighted error is because it does not recognise "ComboBox1" (Is that what you have called it? For the code to work it will have to be that or change the code to whatever you have called the control.
Also, the code sheet nearly always starts with an event you did not want to have. Just leave that sub empty or delete it.
I cannot recall seeing a "Dropdown" control. Is this something you have and I dont?.
That code will have to be in a ComboBox event.
Cant remember if I sent you the worksheet I tested with. If I have it I dont mind sending it to you (Please supply your email.
Chas
Yes if it is ok do sent me the worksheet... Can you not see my email when you click on my username? It's my username follow by hotmail.com
Btw i've kinda solved the problem by refering the comboboxes as shapes instead of combobox (see below)
But still i've one small problem before perfecting this: is it possible to have the "city" combobox to show the revised list whenever "country" combobox is changed? Currently the "city" combobox is empty (unless user clicks it) whenever the "country" combobox is changed.
Thanks for the help thus far.
Sub ComboBox1_Change()
Dim off As Integer
Dim thisCell As String
Dim thisRow As Integer
Dim blah As Shape
Dim blah2 As Shape
Set blah = Worksheets("main").Shapes(2) ' countries combobox
Set blah2 = Worksheets("main").Shapes(3) ' cities combobox
Application.ScreenUpdating = False
Worksheets("countries").Select
thisRow = blah.ControlFormat.ListIndex
Worksheets("countries").Cells(thisRow + 1, 2).Select
blah2.ControlFormat.RemoveAllItems
off = 0
thisCell = ActiveCell.Offset(0, off).Value
Do While thisCell <> ""
blah2.ControlFormat.AddItem (thisCell)
off = off + 1
thisCell = Selection.Offset(0, off).Value
Loop
Worksheets("Main").Select
End Sub
The city box is not empty until you click it. You just dont see the cities unless you click it. What you could do is fll the "text box" of the combo so that you can see the first city.
Do this by adding the line:
Worksheets("main").ComboBox2.Text = thisCell
Just after thisCell= ActiveCell etc. just before going into the Do...While loop
Chas
Will send you the file too.
Hi,
I am facing the same problem and if you feel better please send me the excel sheets and complete working code. Thank you. my address is my id on hotmail.
Hello Farfarid,
I've sent you the spreadsheet, Let me know how you get on as I cannot see where the problem is.
Chas.
