Trying to implement a button to add data to an existing table

  • See attached workbook.


    I need the "Add" button to add the values entered in cells B7 and B8 to the value in the respective employee column as selected from the dropdown list in cell B6.


    If selected "yes", cell B9 should add a 1 to the number in row 4 for the respective employee. If selected "no" it should not add anything to row 4.


    I have tried to research and write the code needed, but I have literally gotten nowhere. I am somewhat excel proficient but not VBA proficient.


    Thank you for your help.

  • Re: Trying to implement a button to add data to an existing table


    Try the attached.


    Note I have added 5 named ranges, for Employee names in Row 1 ("Employees", which is a dynamic named range, so will automatically accommodate any number of Employee Names), then each of cells B6 to B9 is a named range ("Employee", "Hours", "Shifts" and "Overtime").


    I also made the data validation list for cell B6 to use the named range "Employees" and the overtime data validation now has Yes and No hard entered (no need to use a list on a worksheet for just 2 entries)


    Code assigned to the button is:

    Code
    Private Sub CommandButton1_Click()
        Dim lCol As Long
        lCol = Application.Match([Employee], [Employees], 0) + 2
        
        Cells(2, lCol) = Cells(2, lCol) + [Hours]
        Cells(3, lCol) = Cells(3, lCol) + [Shifts]
        If LCase([Overtime]) = "yes" Then Cells(4, lCol) = Cells(4, lCol) + 1
        
    End Sub
  • Re: Trying to implement a button to add data to an existing table


    Works flawlessly. Thank you!


    For my own educational purposes, can you explain the following:


    Code
    lCol = Application.Match([Employee], [Employees], 0) + 2


    Everything else makes sense to me except for this.


    Thanks again.

  • Re: Trying to implement a button to add data to an existing table


    You're welcome.


    That line of code assigns a value (column number) to the variable lCol. it works by matching the selected Employee Name with the complete list of employees (the named range in row 1). The Match function returns the position of the selected employee name in the Employees named range. So, for example if "Employee 2" was selected then the Match function would return 2. Finally, because the named range starts in column C (column 3) and not Column A (column 1) 2 has to be added to the returned result of the Match function to give the worksheet column number of the selected employee.


    That final column number is then used to place the new Hous, Shifts and Overtime in the correct column (the same column as the selected employee column).


    Because Match is not a defined VBA function "Application." has to be placed in front of "Match".


    I hope that makes sense now. :)

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!