Add value to my database if not currently in drop down liast

[ipbr21054]

I open my userform & start to add customers details along with selecting his vehicle from a drop down box.
Sometimes the value isnt present so i need to close userform, add value to worksheet database, open userform then start gain.

So now i wish if this occurs as opposed to doing all the above be able to keep the userform open, add the new value to worksheet database & continue once added by selecting it from drop down.

Some infor for you if needed.
Form is called CloningForm
Drop down box in question is called ComboBox5
Worksheet database is called DATABASE INFO
Column for it to be added is Column E

As values are already in Column E the code will need to add to the end of what is currently there.

I am not sure how the updated value will work in respect of refreshing / closing the drop down just selected.

Thanks

 

[Trebor76]

You have the text "Table5" in the RowSource property field of ComboBox5. The field needs to blank as well as any other ComboBox you want to populate using the code I have given you (like ComboxBox1).

 

[ipbr21054]

Ah ok.
I have now deleted the text from the property field for Combobox1 & also Combobo5 & working.

Can you then advise about this code as i have used your code for 2 Comboxes.
Below the longest line of code shows Table5 & last two rows of code show Combobox5 & Combobox1 List

Private Sub UserForm_Activate()

    Dim tblSource As ListObject
    Dim varComboBoxData As Variant

    Set tblSource = Worksheets("DATABASE INFO").ListObjects("Table5") 'Assumes the data is in Table5 from the 'DATABASE INFO' tab.  Change to suit. `
    varComboBoxData = tblSource.DataBodyRange
    Me.ComboBox5.List = varComboBoxData
    Me.ComboBox1.List = varComboBoxData
End Sub

 

[Trebor76]

I'm not too sure what you mean but I think you're asking how to populate ComboBox1 with Table1 if so then it's the same concept:

Option Explicit
Private Sub UserForm_Activate()

    Dim tblSource As ListObject
    Dim varComboBoxData As Variant

    Set tblSource = Worksheets("DATABASE INFO").ListObjects("Table5") 'Assumes the data is in Table5 from the 'DATABASE INFO' tab.  Change to suit.
    varComboBoxData = tblSource.DataBodyRange
    Me.ComboBox5.List = varComboBoxData
    Set tblSource = Worksheets("DATABASE INFO").ListObjects("Table1") 'Assumes the data is in Table1 from the 'DATABASE INFO' tab.  Change to suit.
    varComboBoxData = tblSource.DataBodyRange
    Me.ComboBox1.List = varComboBoxData
    
End Sub

If not I'll need more info as to what you're after.

 

[ipbr21054]

Ok so before your message above I was using the code supplied for 1 Combobox but then thought to also use it on another.

So the initialise event that I posted mentioned Table 5 followed by Combobox 1 & Combobox 5

I wasn’t sure how to change it so it also had Table 1 mentioned.

Tomorrow I will try the above.

Thanks.

 

[ipbr21054]

Thanks for the help now 99% complete

 

[ipbr21054]

Please take a look & see what is going wrong.

The code for the command button & Userform Activate are shown below.

This is what should happens.

Open form & current values in Combobox1 are 3, 4, 5, 6
I wish to now add 2 so i click the Command button names AddCustChip.
I see the input field so i enter 2 & press ok.
Now at this stage 2 is added to my database on the worksheet & is sorted A-Z, but i dont like the range in the sort being fixed but cant work out how to do the xlpart of it to find last row.
The 2 is also added to the Combobox1 but is at the bottom of the list so like, 3, 4, 5, 6, 2
I was expecting it to be sorted to show in the Combobox 1 as 2, 3, 4, 5, 6
If i close the form & open it again its then sorted but the goal is to not have to close / open the form.

Many Thanks

Private Sub AddCustChip_Click()

    Dim varNewValue As Variant, varComboBoxData As Variant
    Dim lngNewRecRow As Long
    Dim tblSource As ListObject
    
    varNewValue = InputBox("ENTER THE NEW CHIP FOR Col. A:")
    
    If Len(varNewValue) > 0 Then
        lngNewRecRow = ThisWorkbook.Sheets("DATABASE INFO").Cells(Rows.Count, "A").End(xlUp).Row + 1
        ThisWorkbook.Sheets("DATABASE INFO").Range("A" & lngNewRecRow).Value = varNewValue 'Adding a record to a table will automatically resize it
        Set tblSource = Worksheets("DATABASE INFO").ListObjects("Table1") 'Assumes the data is in Table1 from the 'DATABASE INFO' tab.  Change to suit.
        varComboBoxData = tblSource.DataBodyRange
        Me.ComboBox1.List = varComboBoxData
    End If
        With Worksheets("DATABASE INFO")
       .Activate
       Range("A1:A16").Sort Key1:=Range("A1"), Header:=xlYes
    End With

    Application.WindowState = xlMaximized
    
    With Worksheets("CLONING")
       .Activate
    End With
End Sub

Private Sub UserForm_Activate()

    Dim tblSource As ListObject
    Dim varComboBoxData As Variant

    Set tblSource = Worksheets("DATABASE INFO").ListObjects("Table5") 'Assumes the data is in Table5 from the 'DATABASE INFO' tab.  Change to suit.
    varComboBoxData = tblSource.DataBodyRange
    Me.ComboBox5.List = varComboBoxData
    
    Set tblSource = Worksheets("DATABASE INFO").ListObjects("Table1") 'Assumes the data is in Table1 from the 'DATABASE INFO' tab.  Change to suit.
    varComboBoxData = tblSource.DataBodyRange
    Me.ComboBox1.List = varComboBoxData
    
End Sub

 

[ipbr21054]

Think ive sorted it now with this line of code

        With Worksheets("DATABASE INFO")
       .Activate
       Range("Table1").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
       Call UserForm_Activate
    End With