tech on the net

MS Excel: Update a cell when a checkbox is clicked (Forms toolbar) in Excel 2003/XP/2000/97

Question: In Microsoft Excel 2003/XP/2000/97, is there a way to link a check box to another cell using the Forms Toolbar? For example, I've made a checkbox and whenever the checkbox is clicked, I would like the current date to automatically appear in another cell. Is this possible?

Also I would like the date to not change whenever the worksheet is opened. For example, if a cell has been set to July 16th and the worksheet is opened on July 17th, I still want the cell to read as July 16th.

Answer: Because you don't want the cell value changing whenever the spreadsheet "recalculates", we'll have to create a macro to determine when to update the cell value.

Let's look at an example.

Download Excel spreadsheet (as demonstrated below)

Microsoft Excel

In our spreadsheet, there are two columns - column A contains a checkbox and column B contains the "linked" cell.

We've created various checkboxes in column A using the Forms toolbar. To view the Forms toolbar, select Toolbars > Forms under the View menu.

When the checkbox in column A is "checked", the associated cell in column B will be updated with the current system date. When the checkbox is "unchecked", the associated cell value will be cleared.

This is accomplished with a macro called Process_Checkbox. You can view the macro by pressing Alt-F11.

The macro determines what row the checkbox resides in and then "sets the value" of the associated cell in column B.

Macro Code

The macro code looks like this:

Sub Process_CheckBox()

   Dim cBox As CheckBox
   Dim LRow As Integer
   Dim LRange As String
   
   LName = Application.Caller
   Set cBox = ActiveSheet.CheckBoxes(LName)
   
   'Find row that checkbox resides in
   LRow = cBox.TopLeftCell.Row
   LRange = "B" & CStr(LRow)
   
   'Change date in column B, if checkbox is checked
   If cBox.Value > 0 Then
      ActiveSheet.Range(LRange).Value = Date
      
   'Clear date in column B, if checkbox is unchecked
   Else
      ActiveSheet.Range(LRange).Value = Null
   End If
   
End Sub