Add value to table list if not currently present

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,247
Office Version
  1. 2007
Platform
  1. Windows
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

Rich (BB code):
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
2,061
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
4,247
Office Version
  1. 2007
Platform
  1. Windows
No
Two worksheets.
One with values in tables & one is database where a userform opens.

The user form has combo boxes.
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
2,061
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,139
Messages
5,835,632
Members
430,372
Latest member
contentment

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top