Consulting

Results 1 to 13 of 13

Thread: Solved: Any tutorials about adding items by typing into a Combobox?

  1. #1

    Solved: Any tutorials about adding items by typing into a Combobox?

    Anyone know of any good tutorials or articles on users adding items to an existing combobox list by typing them in, particularly regarding proper use of the event structure? Been trawling Google, and just cant find the right search string that will return me anything. Lots of references to disabling typing into a combobox. Thanx

  2. #2
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Try this for a userform:
    [VBA]Private Sub ComboBox1_AfterUpdate()
    Dim Rng As Range, c As Range
    Set Rng = Range("Data")
    Set c = Rng.Find(ComboBox1)
    If c Is Nothing Then
    Rng(Rng.Cells.Count).Offset(1) = ComboBox1.Value
    End If
    End Sub[/VBA]
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  3. #3
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    The Range to populate the combobox will need to be dynamic of course.
    You may wish to add a bit of code to sort the new range if an ordered list is required, or to insert the new item in the correct position.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  4. #4
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    I just offered a start Malcolm. When they don't say if it's a combox for a userform or from a forms toolbar or a validation list it's hard to answer their questions.

    I will wait and let them ask some more questions I guess.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  5. #5
    Quote Originally Posted by lucas
    ...When they don't say if it's a combox for a userform or from a forms toolbar or a validation list it's hard to answer their questions.

    I will wait and let them ask some more questions I guess.
    Point taken.

    It's to be a combobox on a custom userform, using a combobox from the Control Toolbox. It's for updating a Job Specific Materials worksheet from a Product-Supplier worksheet. eg: Product | Supplier | Price | Qty | Etc | Etc, using automatic completion in searching for product names. If the product exists, allowing the user to select the product, if the product doesnt exist, automajically creating a new item in the item list and allowing the user to enter respective details via existing textboxes.

    I understand there's probably a certain level of complexity to this, which is the reason I was asking about tutorials and/or articles/topics to read, particularly in using the Excel/VBA event structure.

    Quote Originally Posted by mdmackillop
    Let us know the size of the task: is it 10 rows/searches or 10,000.
    About 100.

    Does that help?

    [Edit]
    Found a link that discusses the subject for Java:
    http://www.orbital-computer.de/JComboBox/

    And after all that...
    It looks like "MatchEntry Property = 1" is the begining of the answer
    Last edited by websmythe; 04-08-2008 at 11:52 PM.

  6. #6
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,284
    Location
    When your parts list is in another sheet (from which you fill the combobox), I would do the following :

    - You can add something that is not in the list
    - When value of combobox gets inserted in sheet we do a check
    - If we can't find the value we just wrote to the sheet in the list ...
    - We add the value to the list

    - Or you could create an extra button on your form to add the value
    - Need to check if it's present in list (never know who uses it)

    Charlize

  7. #7
    Quote Originally Posted by Charlize
    When your parts list is in another sheet (from which you fill the combobox)
    Ya, the original list starts of as a scan of our existing materials list, but it's usually updated with new products with every job we do. We work with architects and altho most of the products used are usually standard, we need to be able to add dynamically add any new products/materials that they may spec in their drawings, so that we can create estimates.

    Quote Originally Posted by Charlize
    - You can add something that is not in the list
    - When value of combobox gets inserted in sheet we do a check
    - If we can't find the value we just wrote to the sheet in the list ...
    - We add the value to the list
    Makes sense

    Quote Originally Posted by Charlize
    - Or you could create an extra button on your form to add the value
    - Need to check if it's present in list (never know who uses it)
    Actually tying an additem/save to an Ok button makes sense, as the userstring would already be a non-matching element in the list. A little validation and a refresh of the list just might do the trick. Easy peasy. Now all I gotta do is find a lil "sounds like" function to validate the userstring.

    Hey! Maybe there is light at the end of the tunnel ...LOL Thanx.
    When the going gets tough, the tough get weird

  8. #8
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    If you're going to add things in this fashion, you may want to include a confirmation box allowing a final check. A wrongly entered 8 digit part number, for example, could just get "lost"
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  9. #9
    Good point.

    Thats sortta what I meant by a "sounds like" (edit: or "did you mean" function). To handle spell checking, and searching for exisiting items in the list that are suspiciously too similiar.

    Quote Originally Posted by mdmackillop
    If you're going to add things in this fashion...
    I can learn ...How would you have approached it?
    Last edited by websmythe; 04-09-2008 at 09:57 AM.
    When the going gets tough, the tough get weird

  10. #10
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    [VBA]Private Sub ComboBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim Rng As Range, c As Range
    Dim Chk As Long
    Set Rng = Range("Data")
    Set c = Rng.Find(ComboBox1)
    If c Is Nothing Then
    Chk = MsgBox("Check new item - " & ComboBox1.Text, vbOKCancel)
    If Chk = vbCancel Then
    Cancel = True
    Exit Sub
    Else
    Rng(Rng.Cells.Count).Offset(1) = ComboBox1.Value
    End If
    End If
    End Sub
    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  11. #11

    Smile

    Thanx btw, for writing the code.
    K... so did a lil mod on yer VBA to update the combobox. It seems to work, but there's probably a more elegant way. Anyways, I'm not sure if updating the Combobox list here in the _BeforeUpdate event is the best option. The help system seems a lil thin on the Combobox event sequence, and I have read "Where To Place VBA Code" over at http://www.excelguru.ca/node/5 and been searching for other articles, do you know of any good links that talk about the Combobox event firing sequence?

    getSpecialCells_LastRowxlCellTypeTextConstants is a rework of SpecialCells_LastRowxlCellTypeTextConstants() from http://vbaexpress.com/forum/showthread.php?t=9774
    [vba]Private Sub ComboBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

    Dim Rng As Range, c As Range
    Dim Chk As Long
    Set Rng = Range("A2:A" + CStr(getSpecialCells_LastRowxlCellTypeTextConstants))
    Set c = Rng.Find(ComboBox1)

    If c Is Nothing Then

    Chk = MsgBox("Check new item - " & ComboBox1.Text, vbOKCancel)

    If Chk = vbCancel Then
    Cancel = True
    Exit Sub
    Else
    Rng(Rng.Cells.Count).Offset(1) = ComboBox1.Value

    'not sure if this is the best place for the following
    Set Rng = Nothing
    Fill_Combobox1
    'clear the combobox and reload the items
    End If

    End If

    End Sub
    [/vba]
    When the going gets tough, the tough get weird

  12. #12
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Set Rng = Nothing will go before End Sub
    Fill combobox should not be in this routine. Probably in Userform_Initialize
    The idea of the BeforeUpdate event is to allow you to check the entry is correct BEFORE the code moves on; you can rectify an error. One the data is confirmed, you can add an AfterUpdate event code to do something else if required.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  13. #13
    Quote Originally Posted by mdmackillop
    Set Rng = Nothing will go before End Sub
    Fill combobox should not be in this routine.
    The idea of the BeforeUpdate event is to allow you to check the entry is correct BEFORE the code moves on; you can rectify an error. One the data is confirmed, you can add an AfterUpdate event code to do something else if required.
    Thought so.
    I'll keep looking for more stuff to read on event sequences
    Thanx. Much appreciated.
    When the going gets tough, the tough get weird

Posting Permissions

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