Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Typing into combo boxes (Excel 2000)

    Hello Everyone,

    I have created a combo box that has 10 values to choose from however I would like people who access the worksheet
    to be able to type additional values. This is where my problem occurs, I can not type add values, only select from the list.

    Any ideas on what could be wrong?

    Thanks Kindly,

    Kerrie [img]/forums/images/smilies/smile.gif[/img]

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Typing into combo boxes (Excel 2000)

    This functionality is (alas) reserved to comboboxes placed on userforms, which need to be built and programmed from the visual basic editor (alt-F11 from XL)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Typing into combo boxes (Excel 2000)

    If you use a Combobox from the Controls toolbox, you should be able to enter a value that is not included in the FillRange, provided that the MatchRequired property is set to false.

    Andrew C

  4. #4
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Typing into combo boxes (Excel 2000)

    Hi Andrew,

    Thank you for your help [img]/forums/images/smilies/smile.gif[/img] [img]/forums/images/smilies/smile.gif[/img]
    When using combo boxes is it normal that you can only type in one new value? For example, if I open the xls and type
    a new value into the combo, it will save. However if I reopen later and type in other value it overwrites the old one.

    Thanks Kindly,

    Kerrie [img]/forums/images/smilies/smile.gif[/img]

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Typing into combo boxes (Excel 2000)

    Typing it in like this, will put it into the cell, but will NOT add it to the list that already exists

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Typing into combo boxes (Excel 2000)

    Hi Steve,

    What is needed so that it is added to the list?

    Kerrie [img]/forums/images/smilies/smile.gif[/img] [img]/forums/images/smilies/smile.gif[/img]

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Typing into combo boxes (Excel 2000)

    Kerrie,

    You need to capture some event which is triggered by an action on the combo box, such as the LostFocus event. Something like the following should get you started.

    Private Sub ComboBox1_LostFocus()
    Dim rngNewList As Range
    With Me.ComboBox1
    If Trim(.Value) <> "" And .MatchFound = False Then
    Set rngNewList = Union(Range(.ListFillRange), Range(.ListFillRange).Offset(1, 0))
    rngNewList(rngNewList.Cells.Count) = .Value
    .ListFillRange = rngNewList.Address
    End If
    End With
    End Sub

    I attach a sample worksheet. to demonstrate.

    Andrew C
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •