Add value to table list if not currently present

[ipbr21054]

Afternoon.

I use a small userform where comboboxes have values in a drop down that i then select from.
Sometimes when i click the drop down arrow the value i would like to use isnt there, as its new & never been used before.

So now i need to close the userform, add the values to the table where my values are stored & then start all over again.

Can we somehow allow a value to be added to the table if currently not present ?
The form opens up when i use the worksheet called X300 PRO 3 LIST

The values are stored on worksheet called DATABASE INFO
These comboboxes on the form relate to the column on the worksheet.

COMBOBOX 1 = Column A
COMBOBOX 2 = Column E
COMBOBOX 3 = Column B
COMBOBOX 4 = Column C
COMBOBOX 5 = Column D
COMBOBOX 6 = Column F

Private Sub CommandButton1_Click()
   Dim i As Integer
   Dim ControlsArr As Variant, ctrl As Variant
   Dim x As Long
   For i = 1 To 6
      With Me.Controls("ComboBox" & i)
           If .ListIndex = -1 Then
               MsgBox "MUST SELECT ALL OPTIONS", 48, "X300 IMMO LIST TRANSFER"
               .SetFocus
               Exit Sub
           End If
       End With
   Next i
  
   ControlsArr = Array(Me.ComboBox1, Me.ComboBox2, Me.ComboBox3, Me.ComboBox4, Me.ComboBox5, Me.ComboBox6)
  
   With ThisWorkbook.Worksheets("X300 PRO 3 LIST")
       .Range("A4").EntireRow.Insert Shift:=xlDown
       .Range("A4:G4").Borders.Weight = xlThin
       .Cells(4, "G") = TextBox1.Value
       For i = 0 To UBound(ControlsArr)
        Select Case i
           Case 1, 2, 4
              .Cells(4, i + 1) = IIf(IsNumeric(ControlsArr(i)), Val(ControlsArr(i)), ControlsArr(i))
           Case Else
              .Cells(4, i + 1) = ControlsArr(i)
              ControlsArr(i).Text = ""
        End Select
       Next i
   End With
  
   Application.ScreenUpdating = False
   With Sheets("X300 PRO 3 LIST")
       If .AutoFilterMode Then .AutoFilterMode = False
       x = .Cells(.Rows.Count, 1).End(xlUp).Row
       .Range("A3:G" & x).Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlGuess
   End With
   ActiveWorkbook.Save
   Application.ScreenUpdating = True
   Sheets("X300 PRO 3 LIST").Range("A4").Select
   MsgBox "DATABASE HAS NOW BEEN UPDATED", vbInformation, "SUCCESSFUL MESSAGE"
   Unload X300ImmoListForm
   Range("A5").Select
   Range("A4").Select
End Sub

 

[Jeffrey Mahoney]

It looks like you have Comboxes in cells, not on a form, correct? I use ActiveX comboboxes. They have more properties to control things. For instance, the MatchRequired property allows a user to put a value not in the list. Then it's up to you to go check your database to see if the item is there, then ask the user if they want to add that entry.

I would create a dynamic named range for your list of acceptable values, then you wouldn't have to continue changing the range for the combobox.

 

[ipbr21054]

No
Two worksheets.
One with values in tables & one is database where a userform opens.

The user form has combo boxes.

 

[Jeffrey Mahoney]

On a simple form I created a combobox and a button. If I monitor the "AfterUpdate" trigger, it will allow me to do something after the combobox loses control. With the "MatchRequired" parameter set to false on the combobox I can enter anything into the cell. It's up to you to test if the value entered is in the database and ask the user if they want to add that value.

Private Sub ComboBox1_AfterUpdate()
  MsgBox "Go check to see if it's in the database"
End Sub

Private Sub UserForm_Initialize()
  ComboBox1.AddItem "Yes"
  ComboBox1.AddItem "No"
  ComboBox1.AddItem "Maybe"
  ComboBox1.MatchRequired = False
End Sub

 

[ipbr21054]

Thanks very much