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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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
Back
Top