Capturing date that a Tick box was ticked

[Charlie_Chalk]

Hi

I have a spread sheet containing work in progress, can be anything from 5 to 50 jobs, one job per row.

From time to time these jobs go on hold. I would like to have a tick box to be ticked if they are on hold. I would also like to show how long they have been on hold.

For me to show how long they have been on hold I need to somehow capture the date the tick box was ticked, can anyone please help with this?

CC

 

[Glaswegian]

Hello

You could use this – it will put the date and time that the checkbox was ticked into a cell. Change the cell reference to suit. It assumes that your checkbox was created from the Control Toolbox. Note that if you uncheck the box and then check it again, the date and time will change.

Private Sub CheckBox1_Click()
If CheckBox1 = True Then
Range("E2").Value = Date & " " & Time
End If
End Sub

HTH

Regards

 

[Charlie_Chalk]

I'm sure I'm doing something realy stupid but...

I can't seem to assign this macro to the check box, it keeps saying the macro cannot be found.

Any help appreciated

 

[Charlie_Chalk]

Sorry! Ignore me, I've just noticed the bit about the Control Toolbox.

All working perfectly!

 

[Glaswegian]

Choose ‘Edit’ on the Control Toolbox – the icon with the blue triangle – make sure it is pressed in. Now you will be able to right click on the checkbox – select ‘view code’. The VB editor will open and you will now see the Click Event for the checkbox. Copy the code and paste there.

Regards

Edit: You sorted that too quickly!!

 

[Charlie_Chalk]

Hi

All working OK, but by using the Control toolbox tickbox it won't move with the cells around it, is there any way of using the Forms tickbox?

Please excuse my ignorance if this is a stupid question

CC

 

[Glaswegian]

Richt click on the checkbox and choose 'Format Control' then choose the 'Properties' tab. You then have some options on the positioning of the checkbox.

HTH

Regards