Add Item To Combobox On Sheet

  • I want to add combobox to my sheet in vba code, then I hope I can add some items to this combobox.


    I knew how to add combobox to sheet, but I couldn't find any information about how to add item to that combobox, does anyone help me?

  • Re: Add Item To Combobox On Sheet


    Try this
    [vba] With ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
    DisplayAsIcon:=False, Left:=69.75, Top:=56.25, _
    Width:=98.25, Height:=20.25)

    With .Object
    .AddItem "One"
    .AddItem "Two"
    .AddItem "Three"
    End With
    End With[/vba]

  • Re: Add Item To Combobox On Sheet


    You could take the easy way out and use Data Validation and get your ComboBox when the cell is selected without the code.


    You'd first have a list of what you want in the sheet. Select the cell in which you want the cell to appear. Then, go to Data -> Validation -> Settings -> List. Then select the cells where your list is at. After clicking OK, whenever you select the cell, you'll be greeted with a dropdown box in the list.


    There is a shortcoming in that another sheet may not reference this. However, if the sheet isn't too terribly complex, you can use the indirect function to override that concern. (I say not "too complex" because I understand the indirect function, as well as arrays and vlookups, may use some system resources if you've got a lot of them.)


    Just a thought.


    Quote from mikezang

    I want to add combobox to my sheet in vba code, then I hope I can add some items to this combobox.


    I knew how to add combobox to sheet, but I couldn't find any information about how to add item to that combobox, does anyone help me?

  • Re: Add Item To Combobox On Sheet



    Thansk a lot. My next question is how to handle change event, the calculation will be done automatically when I select a new item in combobox, I don't know how to add the event handler, although I knew how to handle for combobox inside userform.

  • Re: Add Item To Combobox On Sheet


    But I have to handle combobox events, not sheet event, how can I do it?
    When I add a new combobox in dynamic, theie will be a new event handler added, I am not sure how I can do it, can you give me some detail codes?

  • Re: Add Item To Combobox On Sheet


    When I use StartLine = .ProcStartLine(vName, vbext_pk_Proc), I got no vbext_pk_Proc error, why? I am using Excel 2000, may I have to use later 2002?

  • Re: Add Item To Combobox On Sheet


    Did you include reference to extensibility library?


    Rather than adding controls and events on the fly can you add them manually then control whether they are visible or not.
    Perhaps if you explain your problem we can suggest alternative approaches.

  • Re: Add Item To Combobox On Sheet


    Well, Here is my way to handle combobox on fly as below:
    The AddCtrl, DeleteCtrl, AddProc is ok, but I can't compile DeleteProc, can you helpme?


  • Re: Add Item To Combobox On Sheet


    I found new problem. DeleteProc dosn't work, the event handler does still exists. do you have any idea?

  • Re: Add Item To Combobox On Sheet


    Is all you want is the code in the AddProc routine you can forget about the need to generate event code and just use the builtin property LinkedCell.


    [vba] oCombo.LinkedCell = vCell.Address[/vba]

  • Re: Add Item To Combobox On Sheet


    Thanks for your idea.
    But I need to recalculate when I change the item in comboBox, I tried your method, it looks like that no any recalculation when I change item, is it true?

  • Re: Add Item To Combobox On Sheet


    I found linkedcell will change the value in cell, but the event sheetchange doesn't happen, I need that event to recalculate, any idea else?

  • Re: Add Item To Combobox On Sheet


    If the combobox has a linked cell of B2 you could place a formula in C2 which is
    =B2


    Then when the combobox value changes C2 recalculates and the Worksheet_Calculate event occurs.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!