Projects Possible

This is the first in a series of a few articles that I will be sharing on little tips and tricks in Microsoft Excel that I have accumulated or built over the last few months.  I’ve incorporated most of these into an Excel workbook or two to meet my specific needs.  I’m not really a Visual Basic for Applications programmer so I probably won’t be able to help out much in customizing…I just tweak things a bit here and there with help from online resources like Excel Forums. 

In this post, I want to show you how you can have a series of checkboxes in a column and when you “check” the checkbox it populates the cell directly to the right of the checkbox with the current date.   I use this in a project management spreadsheet where I have any number of projects all with common tasks that must be completed.

If you look at the spreadsheet above you will see that there are 3 columns… what you don’t see over to the far left in column A is the name of the task that I want to complete…  When I create a new project this portion of my spreadsheet is populated with a series of expected dates based on a set of known criteria.   I want to be able to come to this worksheet and not only quickly review a project and what needs to be completed but I want to be able to quickly update the “work” that was done on a project and have it give me the date that it was completed.   This is where my checkbox routine comes into play…  by simply clicking on the checkbox I can indicate the task is complete and it will make a permanent record of when it was completed… If I check it by mistake I can uncheck and it will also remove the date.

So how do I do this?

Sub Process_CheckBox()

Dim cBox As CheckBox
Dim LCol As Long
Dim LRow As Long
Dim Rng As Range

  LName = Application.Caller
  Set cBox = ActiveSheet.CheckBoxes(LName)

'Find row that checkbox resides in
  LCol = cBox.TopLeftCell.Column
  LRow = cBox.TopLeftCell.Row
  Set Rng = ActiveSheet.Cells(LRow + 1, LCol + 1)

'Change date in cell to the right of CheckBox, if checkbox is checked
If cBox.Value > 0 Then
  Rng.Value = Date

'Clear date in column B, if checkbox is unchecked
Else
  Rng.ClearContents
End If

End Sub

The key lines here are:

• Set Rng line which determines the row and column to place the date… You may tweak these to be +2, +3, whatever you need based on where and how you place yoru checkboxes in your spreadsheet and where and how you want your dates to appear.
• Rng.Value sets the date if there is a check in the checkbox
• Rng.ClearContents clears the date if the checkbox does not have a date

To use the routine you must open the Visual Basic Editor in Excel – Alt+F11, Click on Insert, Select Module and then Paste the Code above.   In your spreadsheet you would then right click on your checkbox and Assign a Macro to it… the macro is called Process_Checkbox.  Now every time you check the checkbox it will run this macro and it will insert the date.