Userform ComboBox - Autofill if no value selected; Recognize new values not in source

[kmcdon21]

Hi,

Longtime Chandoo reader, first time posting in the forum. I searched through all 'ComboBox' questions in here and didn't see what I am looking for; apologies if it's a repost..

I have a userform with multiple ComboBoxes that pull the dropdown values from tables within my excel file. Some of these boxes are required (see code below), while others are there in case the data entry person needs to add an extra entry (i.e. Insurance 1, Insurance 2, etc). How can I tell the ComboBox to enter "None", "N/A" or whatever if the data entry person doesn't select a value for the boxes that are not required? I'd rather not have blank cells in the output data, just knowing how xlDown and other code can get tripped up with that...

Secondly, is there a way to recognize when new values that aren't in the row source data tables are entered in the ComboBox dropdown? We need the option to enter a new value in the box if it isn't in the list already (I know ComboBoxes can do this), but I want to recognize when these new values are entered so I can add them to the row source data tables (so the new value would be in the dropdown next time).

Private Sub CommandButton_Submit_Click()
   
    'Check if required cells are populated
   
    If TextBox_FirstName.Value = "" Or TextBox_Lastname = "" Then
        MsgBox "Please fill in all required fields"
    End If

    If TextBox_EmployeeName = "" Or ComboBox_Ins1 = "" Then
         MsgBox "Please fill in all required fields"
    End If
   
    If ComboBox_Location = "" Or ComboBox_Test1 = "" Then
           MsgBox "Please fill in all required fields"
    End If
   
    If ComboBox_Ref1 = "" Then
         MsgBox "Please fill in all required fields"
    End If
   
    'Copy input values to sheet.
   
    Dim lRow As Long
    Dim ws As Worksheet
   
    Set ws = ActiveWorkbook.ActiveSheet
   
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        .Cells(lRow, 1).Value = ComboBox_EntryMonth.Value
        .Cells(lRow, 2).Value = ComboBox_EntryDay.Value
        .Cells(lRow, 3).Value = ComboBox_EntryYear.Value
        .Cells(lRow, 4).Value = TextBox_EmployeeName.Value
        .Cells(lRow, 5).Value = TextBox_Lastname.Value
        .Cells(lRow, 6).Value = TextBox_FirstName.Value
        .Cells(lRow, 7).Value = ComboBox_Location.Value
        .Cells(lRow, 8).Value = ComboBox_ApptMonth.Value
        .Cells(lRow, 9).Value = ComboBox_ApptDay.Value
        .Cells(lRow, 10).Value = ComboBox_ApptYear.Value
        .Cells(lRow, 11).Value = ComboBox_Test1.Value
        .Cells(lRow, 12).Value = ComboBox_Test2.Value
        .Cells(lRow, 13).Value = ComboBox_Test3.Value
        .Cells(lRow, 14).Value = ComboBox_Ins1.Value
        .Cells(lRow, 15).Value = ComboBox_Ins2.Value
        .Cells(lRow, 16).Value = ComboBox_Ins3.Value
        .Cells(lRow, 17).Value = TextBox_Auth.Value
        .Cells(lRow, 18).Value = ComboBox_Ref1.Value
        .Cells(lRow, 19).Value = ComboBox_Ref2.Value
        .Cells(lRow, 20).Value = TextBox_Notes.Value
       
    End With
    'clear form after submitting
        TextBox_Lastname.Value = ""
        TextBox_FirstName.Value = ""
        ComboBox_Location.Value = ""
        ComboBox_Test1.Value = ""
        ComboBox_Test2.Value = ""
        ComboBox_Test3.Value = ""
        ComboBox_Ins1.Value = ""
        ComboBox_Ins2.Value = ""
        ComboBox_Ins3.Value = ""
        TextBox_Auth.Value = ""
        ComboBox_Ref1.Value = ""
        ComboBox_Ref2.Value = ""
        TextBox_Notes.Value = ""
       
End Sub

Private Sub CommandButton_Cancel_Click()
    'Close UserForm
    Unload Me
   
End Sub

Thanks in advance

 

[Luke M]

"How can I tell the ComboBox to enter "None", "N/A" or whatever if the data entry person doesn't select a value for the boxes that are not required?"
In the combo box's properties, you can set the default starting value, which you could set to be "N/A"


" is there a way to recognize when new values that aren't in the row source data tables are entered in the ComboBox dropdown?"

Yep! Code structure would be like:

If Not Me.ComboBox1.MatchFound Then
   MsgBox "New Entry"
End If

 

[kmcdon21]

@Luke M Thanks very much for your help. The 'Value' property was an easy solution..

As to the second question, I will not be the user of this form most of the time. I'm making it and passing it off to others to use. With the MsgBox solution you presented, it only alerts the user at that time that it is a new value. Is there a way for the system to recognize it is new and paste that value in a datasheet somewhere? In a sense, it would be logging these new values that need to be added into the dropdown row source data.

 

[Luke M]

Correct, I only gave the code so that you can see how the If statement should be. Once you know that the value in the combo box is new, you can do whatever it is you want. Sounds like you need to write some code that after user "submits" the data, check each combo box for MatchFound, and if false, then add the value to your sheet. Not knowing what your exact form or workbook layout was, I wasn't going to take a guess at writing all the code.