Combo box new entry update list

[hedgehog88]

Hello all,

First off I have been using this forum for a 2 weeks as a resource and was able to complete 50% of my VBA project. So thank you to all that have contributed their answers for us newbies to look up

Now, I don't know why I couldn't find the answer to my question (probably wrong choices of keywords to search) so this will be my first thread question.

I have a userform with a couple of comboboxes populated with lists. I would like have these comboboxes be able to update its list with new entries when type in. Does that make sense?

For example, Column A has 3 entries:

AA
BB
CC

The combobox associated with that list should be able to add "DD" as the 4th entry when entered, and added after applying the add command. So after going back to the sheet with the list it should be:

AA
BB
CC
DD

I hope what I'm asking is understandable. Any confused, please let me know and I'll, again, try my best to explain it.

Thanks in advance.

 

[Rasm]

Try this - it will update the listbox when you move to another cell - it also checks that the entry in column A is 2 characters long - dont know if you want that - but you almost have to move to another cell - how else do you know when you are done - ubless you have that logic - you have to change cell.

ThisWorkBook

Public LastTargetRow As Long
Public LastTargetCol As Long
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    'Offset only required if first row is header
    Select Case LastTargetCol
        Case 1
            If LastTargetRow - Offset > NameForm.NameListBox.ListCount Then
                If Len(ActiveSheet.Cells(LastTargetRow, LastTargetCol)) = 2 Then
                    NameForm.NameListBox.AddItem ActiveSheet.Cells(LastTargetRow, LastTargetCol)
                End If
            End If
    End Select
 
    LastTargetCol = Target.Column
    LastTargetRow = Target.Row
End Sub

 

[hedgehog88]

Thanks for the reply.

Im not sure what to change in the coding. What would I need to replace with my worksheets?

 

[Norie]

How are you currently populating the comboboxes?

From ranges on a worksheet?

You mention an 'Add' button - do you want the user to click that if they want to add a new item to the list?

Or do you want to check if what the user has entered to see if it's in the existing list and give the user the option to add it?

 

[hedgehog88]

How are you currently populating the comboboxes?
From ranges on a worksheet?

Im using the "UserForm_Initialize()" to give me the dyanmic range list

Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("worksheet")

and

"cmdAdd_Click()" to help me add my selection to a worksheet

Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("worksheet")

No. There is a list already. I just want to have the option to add additional data if it isn't present in the existing list.

You mention an 'Add' button - do you want the user to click that if they want to add a new item to the list?

Yes, I have an add command which is already in play.

Or do you want to check if what the user has entered to see if it's in the existing list and give the user the option to add it?

No checking, autocomplete can already check to see what is presently on the list.

I used codes from: http://www.contextures.com/Excel-VBA-ComboBox-Lists.html

 

[Norie]

Autocomplete won't really check if the item is on the list.

You can set the properties of the combobox so that the user gets an error message if the item isn't on the list.

To actually do anything, eg add the new item to a list, you would need code to check that yourself and the take appropriate action.

When the user hits the Add button you can check to see if what they've entered is on the list by looking at the combobox's ListIndex property.

Not on the list - ListIndex = -1.

So something like this:

Option Explicit
 
Private Sub CommandButton1_Click()
Dim ans
 
    If ComboBox1.ListIndex = -1 Then
 
        ans = MsgBox("Item entered not on list. Do you wish to add it?", vbYesNo)
 
        Select Case ans
            Case vbYes
                ' code to add item to bottom of list
                Cells(Rows.Count, Range("AlertList").Column).End(xlUp).Offset(1) = ComboBox1.Value
               
               ' resize list to include new item
                Range("AlertList").Resize(Range("AlertList").Rows.Count + 1).Name = "AlertList"
 
               ' populate combobox with list, including new value
                ComboBox1.List = Range("AlertList").Value

            Case Else
 
                ComboBox1.Value = ""
 
        End Select

    End If
 
End Sub