Click a Check Box to Run a Macro

In the last post, we set up a To Do list with check boxes. Each check box is linked to a cell that’s 2 columns to the right, and we used a macro to set that up quickly.

checkboxtodolist08

Today, we’ll create a macro that puts today’s date in the Date column, if you check a box in column B.

Create the Date Macro

The macro will be relatively simple.

  • We have to figure out where the check box is, when it’s clicked.
  • Then, we’ll find the cell that’s two columns to the right.
  • In that cell, we’ll enter the current date, if the check box is checked.
  • If the check box was cleared, we’ll clear the date cell.

Here is the code, which uses Application.Caller to identify which check box was clicked. If the box is checked, its value is 1, and we’ll put in the date.

Sub CheckBoxDate()
Dim ws As Worksheet
Dim chk As CheckBox
Dim lColD As Long
Dim lColChk As Long
Dim lRow As Long
Dim rngD As Range
lColD = 3 'number of columns to the right for date

Set ws = ActiveSheet
Set chk = ws.CheckBoxes(Application.Caller)
lRow = chk.TopLeftCell.Row
lColChk = chk.TopLeftCell.Column
Set rngD = ws.Cells(lRow, lColChk + lColD)

Select Case chk.Value
   Case 1   'box is checked
      rngD.Value = Date
   Case Else   'box is not checked
      rngD.ClearContents
End Select

End Sub

Assign the Macro to a Check Box

First, we’ll see how to manually set up the check box so it runs the CheckBoxDate macro.

  • Right-click the check box in cell B4, so the pop-up menu appears (if the Move/Copy popup appears, try right-clicking again)
  • Click the Assign Macro command

checkboxtodolist09

In the list of macros, click CheckBoxDate, then click OK

checkboxtodolist10

Test the Date Macro

To test the macro, follow these steps:

  • Click away from the check box, to deselect it
  • Click the check box, to add a check mark, and the current date should appear in the date column
  • Click again, to remove the check mark, and the date cell should be cleared.

checkboxtodolist11

Use VBA to Add Macros to All Check Boxes

Instead of manually assigning a macro to each check box, we’ll create a macro that’s similar to the one we used to link all the check boxes to a cell.

In the macro, we’ll set the OnAction property to the date macro name

Sub SetCheckBoxesMacro()
Dim chk As CheckBox

For Each chk In ActiveSheet.CheckBoxes
   chk.OnAction = "CheckBoxDate"
Next chk

End Sub

Run the Add Macros Code

To assign the CheckBoxDate macro to each check box, run the SetCheckBoxesMacro macro.

You won’t see a change on the worksheet, but now each check box has the macro assigned to it.

To test the results, click on any one of the check boxes, and the Date cell in that row should change.

checkboxtodolist12

Download the Sample File

To see all the code, and the To Do list with check boxes, you can download the sample file from my Contextures website.

On the Sample Files page, go to the UserForms and VBA section, and look for UF0024 – Click Check Box to Run Macro

The zipped file is in xlsm format, and contains macros.

________________________

You may also like...

3 Responses

  1. Petty Officer Logan says:

    Ms. Dalgleish:

    I’ve been attemtping to create an excel spreadsheet to maintain my ship’s (US Navy) gun quals. Unfortunately I lack that skills to accomplish what I would like. I would like to check a box and upon checking this box it will run a macro that will add 16 months to a date in a cell, but if left unchecked will only add 10 months to that date. The trick to this is I would like it to change that in the cell it is entered in this format 19-Jun-14. Any assistance would be much appreciated.

    V/R
    MM2 Logan

  2. Ashmita says:

    I want to know, how to write a macro that will do the following:
    1. suppose I have a worksheet, and i have few checkboxes on it. I select two checkboxes, and click a button, I want a new sheet to open, with the checked in values of the previous sheet.
    is it possible?

  3. Han says:

    Thx~it suitable for my worksheet~!

Leave a Reply

Your email address will not be published. Required fields are marked *