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
 

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,730
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,084,743
Messages
5,379,565
Members
401,613
Latest member
KarimK9

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top