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]

Hi there,

Here's one way (you will have to add a new button which I've called "cmdAddNew"):

Option Explicit
Private Sub cmdAddNew_Click()

    Dim varNewValue As Variant
    
    varNewValue = InputBox("Enter the desired value for Col. E:")
    
    If Len(varNewValue) > 0 Then
        ThisWorkbook.Sheets("DATABASE INFO").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = varNewValue
    End If

End Sub

You may have to also reset the range for ComboBox5.

Regards,

Robert

 

[ipbr21054]

Morning,
The new value is added to the column fine but i dont see it in the drop down.
How would i reset the combobox /
I added Combobox5.Clear but just gave me an error.

Please advise Thanks

 

[Trebor76]

Hi there,

Try this:

Option Explicit
Private Sub cmdAddNew_Click()
    
    Dim varNewValue As Variant
    
    varNewValue = InputBox("Enter the desired value for Col. E:")
    
    If Len(varNewValue) > 0 Then
        ThisWorkbook.Sheets("DATABASE INFO").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = varNewValue
        Me.ComboBox5.AddItem varNewValue
    End If

End Sub

If this doesn't work please post the code you're using to populate ComboBox5.

Robert

 

[ipbr21054]

I get permission denied,
Debug to this being the issue

Me.ComboBox5.AddItem varNewValue

The value is added to the list on worksheet but fails i assume at combobox

 

[Trebor76]

I get permission denied,

That's odd as it worked for me How do you initially populate the combo box when the form is opened?

 

[ipbr21054]

Hi,
Not at home at present but from memory when i right click Combobox5 properties at Row Source it showed Table 5

If we need to delete it from row source & add it another way then let’s do it so it’s working.

Thanks.

 

[Trebor76]

If the data is in a table try this:

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

End Sub
Private Sub cmdAddNew_Click()
    
    Dim varNewValue As Variant, varComboBoxData As Variant
    Dim lngNewRecRow As Long
    Dim tblSource As ListObject
    
    varNewValue = InputBox("Enter the desired value for Col. E:")
    
    If Len(varNewValue) > 0 Then
        lngNewRecRow = ThisWorkbook.Sheets("DATABASE INFO").Cells(Rows.Count, "E").End(xlUp).Row + 1
        ThisWorkbook.Sheets("DATABASE INFO").Range("E" & lngNewRecRow).Value = varNewValue 'Adding a record to a table will automatically resize it
        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
    End If

End Sub

 

[ipbr21054]

I get the same denied message now when i just open the fofrm.

Shall i upload my workbook ?

 

[ipbr21054]

Ive uploaded file here.

You will see on my form ive added 2 new command buttons using your code.
Thanks

DOWNLOAD HERE