Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 13th, 2012, 08:08 AM
BRG BRG is offline
Registered User
 
Join Date: Feb 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Exclamation Excel VBA Employee Project Help Please

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.
 
Old March 7th, 2012, 10:13 AM
Registered User
 
Join Date: Mar 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Solving problem

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!





Similar Threads
Thread Thread Starter Forum Replies Last Post
#NAME? error running Excel 2003 VBA in Excel 2007 steveburn Excel VBA 0 October 24th, 2009 08:47 AM
Help! Conversion Excel 2003 VBA codes to Excel 2007 sunny76 BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2 0 August 13th, 2009 05:38 AM
Code works in Excel VBA but not Access VBA fossx Access VBA 2 May 21st, 2007 08:00 AM
Excel VBA to SQL & back to VBA edesousa Excel VBA 1 June 1st, 2004 02:39 AM
VBA Project Help aspadda Excel VBA 0 November 11th, 2003 03:40 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.