Creating an automatic timestamp when you check a box (VBA question)

Question

Hi there,

I am new to using VBA/writing script and could use some help. Please note I am using a Mac with Excel version 15.3.

My goal is to create an automatic timestamp in column B when you check a checkbox I have inserted into column A. The timestamp in column B would be adjacent to the checkbox checked in Column A. See a screenshot of my spreadsheet set up below:

Using some other forums, I found someone that used the below script for a similar purpose:

Sub CheckBox_Date_Stamp()

    Dim cbx As CheckBox

        'Application.Caller returns the name of the CheckBox that called this macro

        Set cbx = ActiveSheet.CheckBoxes(Application.Caller)

        '.TopLeftCell returns the cell address located at the top left corner of the cbx checkbox

        With cbx.TopLeftCell.Offset(0, 1)

            'Check the checkbox status (checked or unchecked)

            If cbx.Value = xlOn Then

            ' Checkbox is Checked

                .Value = Date

            Else

            ' Checkbox is unchecked

                .Value = ""

            End If

        End With

End Sub

When I tried this, entered this exact code and said 'run program', I got this error message:  "Run-time error '1004': Application-defined or object-defined error". Can someone point me in the right direction on this? Is this script correct for what I am trying to do, and if so, what's up with the error message? Thanks for your help!

[Moved from     Office/Excel/Mac/Unknown/other]

Answer 1

Assuming your macro is in a General Module

Right-click on the Teacher lecturing CheckBox and "assign macro"

From the list choose CheckBox_Date_Stamp and OK

As written the code will toggle the date on/off.

Gord

Answer 2

Thank you, Gord!

That mostly worked, with a slight glitch. The date appeared, but not in the adjacent cell. It posted in the adjacent cell in the row above (for teacher lecturing, it posted over the 'timestamp' label). Do you know how I can fix that? Is it something in the code?

Also, it stamped the date appropriate. Can I edit the code so that it posts the date and the current time?

Thanks very much.

Answer 3

I don't understand the slight glitch.

This line of code     With cbx.TopLeftCell.Offset(0, 1)

chooses the adjacent cell to the right of the CheckBox cell

Offset(0,1)    means 0 rows up or down and 1 column to the right.

To format the Date change        .Value = Date     into

    .Value = Format(Now, "dd/mmm/yyyy hh:mm:ss")

Gord