Home » Forums » Open Source Learning Center » Question and Answer

cell to display the date

Submitted by Kalop on 6 December, 2012 - 00:17

Probably formula [=TODAY()] explain everything, but I would like to make it continuously changing, let say every 2 minutes, or better on every click command, from chosen date for sample: 1/01/2000 to the chosen date for sample: 31/12/2015.

Is it possible??

‹ VBA project : Graphics with variable data sources Pivot Table / Graph help ›
»

With a VBA ontime macro yes

Submitted by AndyLitch on 6 December, 2012 - 07:16.

With a VBA ontime macro yes it is..

»

Please

Submitted by Kalop on 6 December, 2012 - 07:32.

Could you help with that, preferably with baton to change the date, because simply I have no idea how to do that.

»

Assuming your Now() function

Submitted by AndyLitch on 6 December, 2012 - 07:45.

Assuming your Now() function is in cell C3 the following functions in the sheet module will help

Private Sub Worksheet_Activate()
Range("C3").Calculate
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 3 And Target.Column = 3 Then
Range("C3").Calculate
End If
End Sub

That will update every time the sheet is activated or the cell selected..

»

yes I have

Submitted by Kalop on 6 December, 2012 - 08:56.

yes I have in cell C3 function Now() and I copied your functions in sheet module, but nothing happening.

»

To see any change you

Submitted by AndyLitch on 6 December, 2012 - 08:59.

To see any change you probably need to format cell C3 to display the time as well... The date only changes once a day ;-)

»

I can see

Submitted by Kalop on 6 December, 2012 - 09:24.

I can see now what is the problem. You didn't understand exactly what I mean.
I would like to have cell that change the date on every click, let stay with C3, from 1/01/2000 to 31/12/2015

for sample:

start in C3 with - 1/01/2000
click C3
and C3 shows - 2/01/2000
click C3
and C3 shows - 3/01/2000
and so on.....

»

Change the sheet 1 macro to

Submitted by AndyLitch on 6 December, 2012 - 09:39.

Change the sheet 1 macro to the following :-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 3 And Target.Column = 3 Then
Sheet1.Range("C3").Value = Sheet1.Range("C3").Value + 1
Range("A1").Select
End If
End Sub

Add the following code to a code module

Sub Auto_Open()
Sheet1.Range("C3").FormulaR1C1 = "=TODAY()"
Sheet1.Calculate
End

Run the auto_open macro
Now whenever you select the date in sheet 1 it will increment by one day

»

Thankyou....

Submitted by Kalop on 6 December, 2012 - 11:04.

Many thanks, working tip top

»