Excel Button add value to different sheet


I have workbook for customer booking. I am sharing small part of it to make it simple to answer.

I have two sheets. Entry sheet is used to enter customer ID in cell B2. I want:

  1. the ADD button to add the value entered to be saved in Table sheet in column A.
  2. Each time the user clicks the button the value to added in the Table sheet.
  3. But if same value entered then it should show error message box and ask to enter value again. 
  4. There is another button which clears all the data but I have not added to this file cause I do not need any help with that so ignore it. 

See the attached sheets.



Selected Answer

Here's the code you have been looking for. Install it in a standard code module and assign it to your 'Add' button.

Sub AddCustomer()
    ' 173

    Dim WsIn        As Worksheet            ' "Entry"
    Dim Rng         As Range                ' existing customer IDs in WsOut
    Dim CustId      As Variant
    Dim Rl          As Long                 ' last row

    Set WsIn = Worksheets("Entry")
    CustId = Trim(WsIn.Cells(2, "B").Value)
    If Not CustId = Empty Then
        With Worksheets("Table")
            Rl = .Cells(.Rows.Count, "A").End(xlUp).Row
            Set Rng = .Range(.Cells(2, "A"), .Cells(Rl, "A"))
            If Rng.Find(CustId, , xlValues, xlWhole) Is Nothing Then
                ' write to the next row
                .Cells(Rl + 1, 1).Value = CustId
                WsIn.Cells(2, "B").ClearContents
                MsgBox "The customer ID """ & CustId & """ is already registered." & vbCr & _
                       "Please enter another ID.", _
                       vbInformation, "Duplicate customer ID"
            End If
        End With
    End If
    WsIn.Cells(2, "B").Select
End Sub

Find the code installed, tested and working in the attached workbook.



Waqar (rep: 8) Feb 15, '21 at 9:59 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login