check for duplicate in excel table before adding new

jjjr1501

New Member
Joined
Sep 10, 2019
Messages
1
Hello

I am fairly new to VBA and i am looking to create a tabel without having someone enter duplicate records over and over by hitting the AddReturn button over and over. below is what i am using for the command button. Any Idead what i should add to stop this?



Private Sub AddReturn_Click()



ActiveSheet.ListObjects("Returns").ListRows.Add

ModifyTableRow ExpensesTable.ListRows(ReturnsTable.ListRows.Count).Range

UpdatePositionCaption

End Sub
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,774
Guesses below since there is not enough info provided to be sure.

See comments in code:

Code:
Option Explicit

Private Sub AddReturn_Click()

    'No variables declared, added for clarity
    Dim ReturnsTable As ListObject
    Dim ExpensesTable As ListObject
    
    'No variables assigned, guessing assignment for clarity
    Set ReturnsTable = ActiveSheet.ListObjects("Returns")
    Set ExpensesTable = ActiveSheet.ListObjects("Expenses")
    
    'Add a blank row to the returns table
    ActiveSheet.ListObjects("Returns").ListRows.Add
    
    'Unidentified function asssumed to copy?/move? the selected?? row of the
    '  Expenses table to the just-added row in the returns table
    ModifyTableRow ExpensesTable.ListRows(ReturnsTable.ListRows.Count).Range
    
    'Unidentifed function - unclear what it does
    UpdatePositionCaption

End Sub

'Option 1
'Remove ActiveSheet.ListObjects("Returns").ListRows.Add row
'Add a column to the return table to indicated if it has been copied to the expenses table
'Modify the code in the ModifyTableRow code:
'    to abort processing if initiated with a previously returned row selected
'    to add row and continue if not on a previously returned row
'    to mark the row once the item is returned

'Option 2
'Assume a primary key (unique id) common to the Returns and
'  Expenses table in column 1 of each table

'Add code to search the Returns table primary key column for
'  the primary key value in the selected Expenses table and
'  halt processing if it is found.
 

Forum statistics

Threads
1,089,675
Messages
5,409,665
Members
403,274
Latest member
Lewis1995

This Week's Hot Topics

Top