Only add new value if it doesnt exist already

[IPBR21054]

I have a working code in use & supplied below.
My issue is that it adds the new value even if the value exists,so i end up with many the same value.

What edit can i make so the code only adds new value to table if it doesnt exists.
Example,
If ABC 123 exists do not add BUT if ABC 123 is not in the table then please add it.
I didnt nothice this before so now i see a few ABC 123 values in the same table.

Private Sub AddKeyToTableList_Click()
Dim response As Integer
Dim oNewRow As ListRow
' ADD NEW KEY TYPE TO TABLE
With Sheets("INFO").ListObjects("Table38")
Set oNewRow = .ListRows.Add
oNewRow.Range.Cells(1) = Me.TextBox3.Value

.Sort.SortFields.Clear
.Sort.SortFields.Add KEY:=.ListColumns(1).Range, SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortTextAsNumbers
With .Sort
.Header = xlYes
.Apply
End With
Application.Goto (.HeaderRowRange.Cells(1))
End With
Sheets("INV").Select 'RELOAD INV WORKSHEET
ComboBox1.Value = TextBox3.Value
End Sub

 

[p45cal]

Something like this:?

Private Sub AddKeyToTableList_Click()
Dim response As Integer
Dim oNewRow As ListRow
' ADD NEW KEY TYPE TO TABLE
With Sheets("INFO").ListObjects("Table38")
  If IsError(Application.Match(Me.TextBox3.Value, .ListColumns(1).DataBodyRange.Value, 0)) Then
    Set oNewRow = .ListRows.Add
    oNewRow.Range.Cells(1) = Me.TextBox3.Value

    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=.ListColumns(1).Range, SortOn:=xlSortOnValues, _
                         Order:=xlAscending, DataOption:=xlSortTextAsNumbers
    With .Sort
      .Header = xlYes
      .Apply
    End With
    Application.Goto (.HeaderRowRange.Cells(1))
    Sheets("INV").Select                         'RELOAD INV WORKSHEET
    ComboBox1.Value = Me.TextBox3.Value
  Else
    MsgBox Me.TextBox3.Value & " already exists!"
  End If
End With
End Sub

 

[IPBR21054]

Many thanks it worked well.

Do i need to mark this somehow as solved ?

 

[p45cal]

I don't think there's an official way to do this here at Chandoo. Some people edit the subject/header of their thread by adding the word 'SOLVED'
eg. https://chandoo.org/forum/threads/h...locate-values-to-it-solved.57619/#post-308895

 

[IPBR21054]

May I ask a question please.
Can the code be edited so the button called AddKeyToTableList is only shown if the value isn’t present.
Currently clicking the button informs the user that the value already exists.
Removing the message then shows the button press but user might think nothing has happened.

Hence why I ask to show / hide depending on if value exists etc.

If value is in table hide button but if value isn’t in table the show button.

What do you think ?

Thanks.

 

[p45cal]

That would mean testing for existence with every change in TextBox3 as the user types. I'm too lazy to try and recreate your setup to test so could you attach a workbook with your setup here please?

 

[IPBR21054]

Wouldn’t it be simple ?
When userform opens the code looks at Textbox3 value & if it is in Combobox1 then hide command button. If value isn’t present then Show button.

 

[p45cal]

So textbox3 only needs looking at when the user form opens?! So the user doesn't change textbox3? How does the data get into textbox3?
It's these sorts of question that would need answering, among probably many others, that your workbook would answer straightaway. I could guess, but I'd probably guess wrongly and the answer I'd give would be useless to you.

I haven't any idea what combobox1 contains, how it's updated and how it relates to the table on the spreadsheet.

 

[IPBR21054]

The user opens the user form.
Textbox3 is populated from my worksheet.

Combobox list is from a table which contains values.

Sometimes Textbox3 value will be present in the Combobox list & sometimes it won’t.

I’m trying to hide or show the command button depending on whether Textbox3 value is or isn’t in the Combobox list.

So at the time when the userform is open the code looks at Textbox3 value & checks if it is in the Combobox list.
If it is present hide button.
If it isn’t present the show button.

Combobox list is from Table38 on the worksheet. It’s the table where the value is added if not present.