If value isnt in userform drop down list then allow user to add it

[ipbr21054]

I have a userform which in ComboBox1 has a list of vehicles.
Sometimes when the user wants to make a selection the vehicle in question isnt there.
he then has a long winded workaround.
Close userform, open worksheet, add vehicle to table.
Start all over again.

This post is to allow the user to add the vehicle without the need of all the work.
Not sure how it would work but if vehicle isnt present maybe open an input box.
Type vehicle in question.
Code saves new vehicle to worksheet table list.
User then selects vehicle in ComboBox1
Continues with entering his other values.

Some info for you.
Worksheet where userform is located is called QUOTES
Userform is called QUOTES FORM
Vehicle drop down list is in Combo1
Worksheet where tables are is called INFO
Table location is column B
Table name is Table2

The new entry should be added to the last cell in the list.
Sort columb B from A-Z

 

[6StringJazzer]

If a user types a value into a combobox instead of picking from the list, the ListIndex will be -1 and the Value will be <>"". You can test for this when they click the Submit button (or whatever you have for the user to indicate they are done entering data). If those conditions are met your code can add that value to the table.

 

[ipbr21054]

So the way i understood you advise is,
If the vehicle isnt in the dropdown list in ComboBox1 then just type ROLLS ROYCE in the field in question.
So that is what i did.

I then use a command button to send userform data to worksheet.
I see ROLLS ROYCE in the worksheet fine.

I go to the INFO worksheet & look in column B for ROLLS ROYCE but it isnt there.

If the above understanding is correct please advise what i now need to do in respect of adding it to the table so next time it can be selected from the drop down.

Thanks

Thanks

 

[ipbr21054]

On my userform i have added a command button.
At present i have added this code to it supplied below.

At present when i select this command button the code selects the empty cell after the last entry in the required column.

So now i need to add a value to that cell.
Lets assume the drop down doesnt show the vehicle in question.
The user then types MINI

Can we run the new command button code & have it put the typed value in ComboBox1 in the cell it just selected ?
If so how do i proceed please.

Private Sub CommandButton2_Click()
    Sheets("INFO").Select
    Dim LastBlankRow As Long
    LastBlankRow = Cells(Rows.count, 2).End(xlUp).Row + 1
    Cells(LastBlankRow, 2).Select
    
End Sub

.

 

[ipbr21054]

So this is what works for me albeit dirty but needs somebody to tidy it up please.

Basically when run it will put the value of ComboBox1 in cell B182, This is the first empty cell at the end of the column, I also put B182 fixed range as i didnt know how to just add the value in the found cell.

Below you will see that i would like it to then sort that column from A-Z where row 1 has headers & first value is row 2 then down the page.

It would be nice if this could be done in the background without having to watch the sheet INFO being open etc.
Thanks

Private Sub CommandButton1_Click()
      Sheets("INFO").Select
    Dim LastBlankRow As Long
      LastBlankRow = Cells(Rows.count, 2).End(xlUp).Row + 1
      Cells(LastBlankRow, 2).Select
      Sheets("INFO").Range("B182") = ComboBox1
     ** SORT COLUMN A=Z HERE **
      Sheets("QUOTES").Select
End Sub

 

[ipbr21054]

Update
I have now managed to do the sort part of my code.

The last thing i need to do is in the code below the value of ComboBox1 is being sent to cell B182
I have worked out how to find the cell that the value needs to go in but i cant work out how to put it there.

Private Sub CommandButton1_Click()
      Dim LastBlankRow As Long
      Sheets("INFO").Select
      LastBlankRow = Cells(Rows.count, 2).End(xlUp).Row + 1
      Cells(LastBlankRow, 2).Select
      Sheets("INFO").Range("B182") = ComboBox1.Value
      Sheets("QUOTES").Select
End Sub

 

[ipbr21054]

Got it.

ActiveCell.value=ComboBox1.value

 

[NoSparks]

It would be nice if this could be done in the background without having to watch the sheet INFO being open etc.

You could use of the .MatchFound property of the ComboBox in the _BeforeUpdate event
to bring up a YES-NO message box and go from there.
No need for an Input box to retype what you just typed.
No need for a command button to get you back to where you already are.

This works with my Excel 2010

Private Sub ComboBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim response As Integer
    Dim oNewRow As ListRow
    
' if combo empty stop here
If Len(Me.ComboBox1.Value) = 0 Then Exit Sub

' is entry in drop down
If Not Me.ComboBox1.MatchFound Then
    ' ask if to add entry to list
    response = MsgBox("Entry is not in the drop down" & vbCrLf & _
                      "Do you want to add it ?", vbYesNo)
    
    If response = vbYes Then
        ' add row to table
        With Sheets("INFO").ListObjects("Table2")
            Set oNewRow = .ListRows.Add
            ' put what was entered into list
            oNewRow.Range.Cells(1) = Me.ComboBox1.Value
        End With
        
        '** SORT A=Z HERE **
        With Sheets("INFO").ListObjects("Table2")
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=.ListColumns(1).Range, SortOn:=xlSortOnValues, _
                                    Order:=xlAscending, DataOption:=xlSortTextAsNumbers
            With .Sort
                .Header = xlYes
                .Apply
            End With
        End With
        
        ' assign the new list to combo
        With Me.ComboBox1
            ' to clear combo or not ??
            .Value = ""    ' comment out to already have in combo
            ' maintain focus
            Cancel = True
            ' reload drop down
            .List = Sheets("INFO").ListObjects("Table2").DataBodyRange.Value
            .DropDown
        End With
    
    Else    ' response was NOT vbYes
        MsgBox Me.ComboBox1.Value & vbCrLf & "will be cleared from the ComboBox" & vbCrLf & _
               "and NOT added to the drop down list"
        ' clear entry
        Me.ComboBox1.Value = ""
        ' maintain focus
        Cancel = True
        Exit Sub
    End If
    
End If

End Sub

Private Sub UserForm_Initialize()
    With Me.ComboBox1
        .List = Sheets("INFO").ListObjects("Table2").DataBodyRange.Value
        .MatchRequired = False
    End With
End Sub

My file for you to have a try before doing anything with your own file.

 

[ipbr21054]

Im looking at it but not sure what it actually does or its operation.
I open the userform & look in drop down for value.
The value isnt there so i type the value in Combobox field.

This is the bit im not sure about.
I click in the TextBox field to see the Msg question.
Selecting Yes i see my new value added to list on worksheet & sorted A-Z & see it in combobox.

Trying to get my head around clicking the Textbox.

In my worksheet the userform is on one worksheet & my database list is on another worksheet.

Yours seems much quicker & cleaner than mine & im interested.
Shall i put my file here ?

 

[NoSparks]

The text box has no purpose other than where the focus goes when you leave the combobox.
User forms are not on sheets. You require certain sheets behind the user forms because you keep using select for everything.

If you make a file available I'll have a look but it's 9:30 AM here and I'll be out until about noon.