Need Frozen Time Stamps from clicking checkbox

I am trying to create an electronic inventory sheet. This sheet would have check boxes where the caption contains a description of the material. I need the time each checkbox is "checked" to be recorded on the sheet. The number of check boxes can be very few to several hundred. I can get the check boxes on the spread sheet ok, but I can't get the time to stay frozen.

Here is the code I am using to create the check boxes:

Please Login or Register  to view this content.

I have tried populating an adjacent cell with "=IF(Linkedcell = TRUE, NOW(),"")" but then all the Now()s update whenever one of the boxes is checked or unchecked

I have tried to set up a worksheet_change to replace the now() value, but it doesn't recognize checking the box or the target cell changing from TRUE to FALSE as a worksheet change--it works if I manually change it.

I have tried a little with .OnAction method, but I don't know how to reference the right cell in the code that I call in. (I keep getting lost when reading about how to pass an argument through the .OnAction method.

I am reluctant to build activex checkboxes and assigning code to them with VBE because there could be so many checkboxes and again I am not sure how to correctly reference the right cell.

Any help would be much appreciated.

Thanks,

(I have attached a sample spreadsheet as a visual sample)

Give your check boxes a macro and you can access anything you need.

Please Login or Register  to view this content.

Right-click on the check boxes to assign them a macro, the same macro for every one. Use Application.caller to determine which check box was called and run what code you need to.

Please Login or Register  to view this content.

Copy of Book1.xlsm

Thanks again for your response.

For this use, I or other users don't have time to right click and assign a macro to each individual check box after they have been created. (there will be a lot of check boxes and we will produce this sheet a couple times a day)
I do like the idea of the application caller--I am not sure how to ask this question, but is there a way reference the check box name with variable so I don't have to build a list:

Case "Check box 1"
code
...
...
Case "Check box 500"
code

Let me know if that question doesn't make sense.

Once again I appreciate your help.

This will assign a macro to all of the shapes on the active sheet, assuming you only have check boxes on that sheet that you want to add the macro to. Just change "CheckBox36_Click" to what you name your macro.

Please Login or Register  to view this content.

You could use TopLeftCell or BottomRightCell to find the address of the checkbox, I suggest TopLeftCell.

Please Login or Register  to view this content.

Then knowing the cell address, if the column is G offset the column (based on the sample) by 3 to record your time.

You're welcome. Glad it worked out for you.