Loop Through Data Validation List

  • I have an Excel workbook that is use to keep track of employee attendance. One of the sheets, EE Attendance Calendar, has a Data Validation list in cell A4. The list is of employee names and is pulled from a named range of cells in different sheet, Employee Data, in the same workbook (named range is Name and refers to 'Employee Data'!$A$3:$A$102). When an employee’s name is selected, other cells in the EE Attendance Calendar sheet pull data for that employee from other sheets in the workbook.

    I wanted to email a portion of the EE Attendance Calendar sheet to each employee, so I decided to create a macro I could assign to a button. I know very little about VBA, but after some internet research and using the Record Macro function I managed to piece together code that: copies cells from EE Attendance Calendar and pastes them into a different sheet in a new workbook, completes some formatting, enters formulas into certain cells, saves the new workbook, emails it to the employee, and then deletes the new workbook.

    The part I'm have a problem with is doing all the above for each employee in the Data Validation list with one click of a button. I found this thread with a seemingly similar question:


    I tried to insert the suggested code in my code, but I'm not getting the desired result. When I run the macro, it creates the new file and sends the email for the first employee listed over and over again. I’m pretty sure it would go all day long if I let it. I end up having to use Task Manager to kill Excel. Not sure if I'm not inserting the code in the proper place or if my situation requires different code to handle looping through the names on the list.

    I also found this thread, but I’m not sure how to incorporate the code.


    My code where I tried to incorporate the code in the first thread is below. I can upload the workbook, but I will need to delete employee data. So, thought I would try this way first.

    Let me know if you need any additional information. Your guidance is greatly appreciated!

  • Re: Loop Through Data Validation List

    I forgot to mention that I'm using Excel 2010 and my email application is Lotus Notes 8.5. Not sure the latter matters, but I noticed on some other threads there are differences in functionality between various versions of Excel.

  • Re: Loop Through Data Validation List

    Quote from jujubean6402;768584

    The part I'm have a problem with is doing all the above for each employee in the Data Validation list with one click of a button.

    In the main loop you need to update the data validation cell value with the current employee:

    For Each c In inputRange
            dvCell.Value = c.Value    'ADD THIS LINE

    That will update any cells which depend on the data validation value.

Participate now!

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