Excel VBA Employee Project Help Please

BRG · February 13th, 2012, 08:08 AM

Hi There,

I am currently working on a project to help efficiently track training due dates for the employees of the company that I work for. I am proficient with Excel, just not the programming side, so this is why I came here looking for help. I am using Excel 2007 BTW.

Okay, the deal is, All I really need to have is 3 columns of data, with A Being the employee name, B being the date trained column, and C being the training due column. Columns B & C will contain only dates.

I am looking for a macro that will run when the workbook is opened, and will display a message highlighting the rows of employees who need training either within the LAST 30 days or the NEXT 30 days.

Also, it would be great if the macro could also contain a button to either publish this list of employees to either an Adobe PDF or Word Document.

Thanks for all of your help, and I hope I am not making this too complicated. lol.

AndyOwl · March 7th, 2012, 10:13 AM

Hi

There are various parts to this problem!

To highlight all the cells which are within the date range, you could use something like this:

Code:

Sub HighlightCells()

    Dim TopCell As Range
    Dim DateRange As Range
    Dim DateCell As Range
    
    'find out where top cell is
    Set TopCell = Range("B2")
    
    'get a reference to contiguous block of date cells
    Set DateRange = Range(TopCell, TopCell.End(xlDown))
    
    'loop over all of these cells, colouring if within range
    For Each DateCell In DateRange
    
        If Abs(DateCell.Value - Now) < 30 Then
            DateCell.Interior.ColorIndex = 5
        Else
            DateCell.Interior.ColorIndex = 0
        End If
        
    Next DateCell
    
End Sub

This uses a concept called looping over collections.

To get this to run when you first open your workbook, you need to run the HighlightCells procedure shown above on the workbook's open event. Here's how to attach code to workbook events.

Finally, you could programmatically write everything to Word by creating a reference to it, but if you're using Excel 2007 or later I think you can save a file as PDF.

Good luck!