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

[websmythe]

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

[lucas]

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]

[mdmackillop]

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.

[lucas]

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.

[websmythe]

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

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

[Charlize]

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

[websmythe]

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.

- 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

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

[mdmackillop]

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"

[websmythe]

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.

If you're going to add things in this fashion...

I can learn ...How would you have approached it?

[mdmackillop]

[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]

[websmythe]

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]

[mdmackillop]

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.

[websmythe]

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.