[OTP] VBA Using combobox in Excel

[OTP] VBA Using combobox in Excel

• Neil East

  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...

  
  
  

• SoopahMan

  Hello Farfarid,

  I've sent you the spreadsheet, Let me know how you get on as I cannot see where the problem is.

  Chas.

  
  

• JeevesIndia

  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.

  
  
  

• herbjörn

  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

  
  

• bola shokry

  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

  
  

• Jedrzej

  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

  
  
  

• kscmjo

  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... :)
  
  
  

• rallenh

  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

  
  

• Ilya Tumanov

  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

  
  
  

• Hassan Ayoub

  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.

  
  

• jdt.me.uk

  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

  
  

• hr0nix

  These forums are for VB.NET questions.

  The VB within Excel is Visual Basic for Application. VBA is a very different product from VB.NET and there are some other locations where youy will probably get a quicker and better response to your VBA questions.

  You may find more assistance in following which specifically deals with VBA development.

  http://forums.microsoft.com/MSDN/ShowForum.aspx ForumID=74&SiteID=1

  Or if you are using VBA from within on of the office applications

  Office Automation: office.developer.automation newsgroup

  http://msdn.microsoft.com/newsgroups/default.aspx dg=microsoft.public.office.developer.automation&lang=en&cr=US

  Or the Office Newgroups

  http://www.microsoft.com/office/community/en-us/default.mspx d=1
  

  Hope that helps

  
  

• AzurianArcher

  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
  

  
  

  
  
  
• [OTP] VBA Using combobox in Excel