Linking check box to a formula

[bcwill]

Trying to simplify updating a tracking sheet. Want to link a "check box" to a formula so that instead of true or false being the output it will display the date the box was checked. Any one know of a way to write this type of formula?

 

[wwgmr]

I think you could nest a if statment in your formula that if the check box output is true then run a Today()function, flase = " " or what ever works better.
I hope this helps. If you are using a macro you maybe able to have a click event place the date in cell for you. I am just getting into VB programing so someone else could maybe tell you the correct code for that. Hope this helps or gets you started.

 

[DaleWatson123321]

bcwill,

"wwgmr" is on the right track.

Here's a VBA routine that I've tested (and it works). You can copy it from here, and it of course needs to be assigned to your Check-Box. Right-click and choose "Assign Macro".

Sub ChekBox1_Click()
Dim chek As String
Dim datecell As String
Dim curdate As String

chek = Range("chekcell&quot.Value
If chek = True Then
Range("datecell&quot.Value = Range("curdate&quot.Value
ElseIf chek = False Then
Range("datecell&quot.ClearContents
End If
End Sub

The above routine requires that you assign Range Names to these 3 cells:

1) "chekcell" - assign it to the "Cell Link" cell which is referred to when you right-click on the Check Box, and choose "Format Control".

2) "curdate" - assign it to a cell in which you enter the following formula: =TODAY()

3) "datecell" - assign it to the cell where you want the date displayed.

Final Note: The routine has an "ElseIf" condition which will cause the contents of "datecell" to be cleared if the Check-Box is "un-checked". I provided this as an example - if you don't want this option, then simply delete those two rows relating to the "ElseIf".

If you're not familiar with assigning Range Names, it is QUITE simple: 1) Highlight the cell (or range), 2) Hold down <Control> and hit <F3>, 3) Type the name, 4) <Enter>

If you have any problems, I can email you the example file I created. Just email me, and I'll send it by return email.

(Anyone else is also welcome to a copy of the file)

Please advise as to how you make out.

Regards, ...Dale Watson

WORK: dwatson@bsi.gov.mb.ca
HOME: nd.watson@shaw.ca