Need some help with a Date problem.

Phil K

New Member
Joined
Nov 16, 2005
Messages
5
Hi everyone, i have a small problem with a spreedsheet i'm currently designing. I'm using the TODAY date function linked to a checkbox. So when i tick the checkbox, Todays date appears in cell 'g33'. However, i don't want the date to change, i want it to remain.

i.e. If i ticked the box on the 2nd of January, reload the software on the 5th, i want the date to remain as the 2nd.

I've looked about trying to find a few formulas but have had so far no joy. Any help would be great.

TickBox linked too $AA$4
Cell G33 (where date will appear) =IF(AA4,AA9,AA10)
AA9 =TODAY()
AA10 =-

Cheers for any help/advise you can give me :)
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
You would have to use a macro for the date to remain static.

You mention the checkbox is linked to a cell, so I'm assuming you're using one from the forms toolbar. I would suggest using a checkbox from the control toolbox (View\Toolbars\Control Toolbox) instead; it'd be a *lot* easier to write code for.

You could then use:

Code:
Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then
    Range("G33").Value = Date
Else
    Range("G33").Value = ""
End If

End Sub

That code needs to go in the sheet module. Right-click the tab of the sheet the checkbox is on, select "View Code" and paste the code into the VBE window that opens up.
 
Upvote 0
Ok, this might sound really stupid, but i've never used the control checkbox before, i assume i need to write a macro to get the checkbox ticking? Is there a tutorial where i can read up on how to do that, or is there an option i've missed to make the checkbox 'tickable'.

When i change the value to False/True the checkbox goes Unticked/Ticked. But i want the end user to be able to tick it to bring the date up.
 
Upvote 0
Oh! You may need to turn off "design mode." I always forget about that myself, until I click the box a couple of times and do nothing but select it.

It's the first icon on the left of the control toolbar (has a little pencil and ruler on it). Clicking that toggles design mode on/off.

After that is turned off, you should then be able to check/uncheck the box. And each time you click on it, the code I provided earlier will run and either insert or remove the date from G33 depending on if the box is checked or not.
 
Upvote 0
You should be able to use the code I posted above ("above" being the first reply in this thread :) )
 
Upvote 0

Forum statistics

Threads
1,223,428
Messages
6,172,044
Members
452,444
Latest member
ShaImran193

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
Back
Top