Capturing date that a Tick box was ticked

Charlie_Chalk

Board Regular
Joined
Sep 15, 2002
Messages
93
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Glaswegian

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
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

Board Regular
Joined
Sep 15, 2002
Messages
93
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

Board Regular
Joined
Sep 15, 2002
Messages
93
Sorry! Ignore me, I've just noticed the bit about the Control Toolbox.

All working perfectly!
 

Glaswegian

Well-known Member
Joined
Oct 14, 2003
Messages
1,487

ADVERTISEMENT

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!! :biggrin:
 

Charlie_Chalk

Board Regular
Joined
Sep 15, 2002
Messages
93
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

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
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
 

Forum statistics

Threads
1,148,530
Messages
5,747,234
Members
424,070
Latest member
smanni3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top