add new item to combobox list

[Adz]

Hi,
what i'm trying to do is add items to my list as users enter them.. IE when user starts typing in combobox if there is a match it usually comes up, if it doesnt, i want it to still act the same but also add the new item to the list so that when it's typed again it will appear. think I sorta explained it..
I tried to do it myself(Just learning) and no go.
Here's what I tried,

Private Sub cboWinery_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim r As Range

With Wine.cboWinery
If Not .MatchFound Then
Set r = Sheets("Data").Range("Winery")
r.Offset(r.Rows.Count)(1, 2) = UCase(.Value)

End If
cboWinery = UCase(.Value)
End With
End Sub

Cheers
Adz

 

[Norie]

How about something like this?

Private Sub cboWinery_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim wsData As Worksheet
Dim rng As Range
Dim NewRow As Long
Dim resp
 
    If cboWinery.ListIndex = -1 Then
 
        resp = MsgBox(cboWinery.Value & " is not on the list. Do you wish to add it?", vbYesNo)
 
        If resp = vbYes Then
 
            Set wsData = Sheets("Data")
 
            Set rng = wsData.Range("Winery")
 
            NewRow = Sheets("Data").Cells(Rows.Count, rng.Column).End(xlUp).Row + 1
 
            Sheets("Data").Cells(NewRow, rng.Column) = UCase(cboWinery.Value)
 
            rng.Resize(rng.Rows.Count + 1).Name = "Winery"
 
            cboWinery.List = wsData.Range("Winery").Value
 
        Else
            Cancel = True
        End If

    End If
 
End Sub

This will error if you have populated the combobox using the RowSource property.

 

[Adz]

Hi Norrie,
the code you gave me,
The msg box works, the selections are put onto the sheet they are supposed to.. but, the new word doesn't go into list (on sheet or combobox). I closed and reopened just in case but no luck

 

[Norie]

It worked for me and I set up everything the way I assumed you had it.

A worksheet called 'Data' with a one column named range called 'Winery'.

It added the new item to the worksheet, expanded the named range and refreshed the combobox so it included the new item.

Did I get your setup right, or am I missing something?

Did you change the code at all?

 

[Adz]

Hi Norie,
Sorry for the delay in reply, sleep gets in the way of everything,
Yes, the way you have set it up is correct. There are more ranges that I will do the same thing for but getting 1 right first will be the best idea.
Range "Winery is in column B.

The only thing I can think of is if the rest of the code in userform is cancelling it out or something.

Here's the rest of it.

Private Sub cboWinery_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim wsData As Worksheet
Dim rng As Range
Dim NewRow As Long
Dim resp

If cboWinery.ListIndex = -1 Then

resp = MsgBox(cboWinery.Value & " is not on the list. Do you wish to add it?", vbYesNo)

If resp = vbYes Then

Set wsData = Sheets("Data")

Set rng = wsData.Range("Winery")

NewRow = Sheets("Data").Cells(Rows.Count, rng.Column).End(xlUp).Row + 1

Sheets("Data").Cells(NewRow, rng.Column) = UCase(cboWinery.Value)

rng.Resize(rng.Rows.Count + 1).Name = "Winery"

cboWinery.List = wsData.Range("Winery").Value

Else
Cancel = True
End If

End If

End Sub







Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("WINES")


lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row




If Trim(Me.cboCat.Value) = "" Then
Me.cboCat.SetFocus
MsgBox "Please enter a Category"
Exit Sub
End If


With ws
.Cells(lRow, 1).Value = Me.cboCat.Value
.Cells(lRow, 2).Value = Me.cboWinery.Value
.Cells(lRow, 3).Value = Me.cboBrand.Value
.Cells(lRow, 4).Value = Me.cboName.Value
.Cells(lRow, 5).Value = Me.cboVariety.Value
.Cells(lRow, 6).Value = Me.cboVintage.Value
.Cells(lRow, 7).Value = Me.chkqld.Value
End With


Me.cboCat.Value = ""
Me.cboWinery.Value = ""
Me.cboBrand.Value = ""
Me.cboName.Value = ""
Me.cboVariety.Value = ""
Me.cboVintage.Value = ""
Me.chkqld.Value = ""
Me.cboCat.SetFocus

End Sub

Private Sub cmdClose_Click()



Unload Me

End Sub


Private Sub cmdClear_Click()

Call UserForm_Initialize

End Sub




Private Sub UserForm_Initialize()
Dim cCat As Range
Dim cWine As Range
Dim cBrand As Range
Dim cName As Range
Dim cVariety As Range
Dim cVintage As Range

Dim ws As Worksheet
Set ws = Worksheets("Data")

For Each cCat In ws.Range("Cat")
With Me.cboCat
.AddItem cCat.Value

End With
Next cCat

For Each cWine In ws.Range("Winery")
With Me.cboWinery
.AddItem cWine.Value
End With
Next cWine

For Each cBrand In ws.Range("Brand")
With Me.cboBrand
.AddItem cBrand.Value
End With
Next cBrand

For Each cName In ws.Range("Name")
With Me.cboName
.AddItem cName.Value
End With
Next cName

For Each cVariety In ws.Range("Variety")
With Me.cboVariety
.AddItem cVariety.Value
End With
Next cVariety

For Each cVintage In ws.Range("Vintage")
With Me.cboVintage
.AddItem cVintage.Value
End With
Next cVintage




Me.cboCat.SetFocus

End Sub


Thanks again for all your help
Adz

 

[Norie]

I can't see anything in that code that would 'cancel' the code I posted out.

Try putting a breakpoint in the initialise event, you can do that by selecting a line of code and hitting F9.

Now when you open the form code you should go into debug mode and you can then step through the code with F8 to see what's going on.

That should give you a start to finding what the problem is, so try it and post back.

Actually as I've been typing I had one idea - how are you creating the named ranges in the first place?

 

[Adz]

Hi Norie,
as a matter of chance, I was manually entering more items in the list and when I hit sort a-z the test items appeared. not sure why but they must of been a long way down. although when I tried to retype test in combobox it didn't reappear. Maybe it was way to far down. I selected entire column, cleared contents just in case it was thinking there was something in cells and tried again... PERFECT!!

sincerely, Thank you very much Norrie.

Cheers,
Adz