Skyline Owners Forum banner

1 - 12 of 12 Posts

·
2.8 HKSステップ3 、
Joined
·
3,072 Posts
Discussion Starter #1
I am after some info.. I have seen it done so I know its possible , really simple I jjust cant think how its done...

I want to populate a cell in a worksheet with the date that the cell is updated but I need it done automatically... so when the cell is clicked the date appears... or the spreadsheet updated and saved the date appears .. you get the idea...

Any help appreciated... TA!
 

·
Registered
Joined
·
592 Posts
You can use '=NOW()', this will fill the cell with the current date, it will only be updated when the sheet is recalculated, or the cell is used in a macro. Is this any good for what you want?

:cheers:
Simon.
 

·
2.8 HKSステップ3 、
Joined
·
3,072 Posts
Discussion Starter #3
Thanks, I have that and =today() but that requires it to be manual , same as control and ; gives the date, if there is no calculation as such on the sheet how can =today() be updated to cover each entry...?
 

·
Registered
Joined
·
5 Posts
Don't know if this is any use, but it will update the current date/time (using =NOW() ) in a cell when the cell is clicked.

Basically, record a macro:
Start recording
enter "=NOW()" in the cell you want
stop recording

Then using the "insert picture/autoshapes" command, draw a little square on (say) the top left of the same cell.

Then right click on the square and select "assign macro" to the square, and assign the macro you've just recorded.

After that, every time you click on the square, it will run the macro, re-pasting the formula in the cell, and so update the time/date.

Finally, rather than just have the square in one corner, you could re-shape the square to be the exact shape of the cell. This will blank out the date/time behind. Then re-format the shape (right-click/format autoshape) so that the "fill" is set to "no fill". The square will now be transparent, and you can see the date/time again - which will be updated each time you click on it.

Dunno if it's any use?

Conrad.
 

·
Registered
Joined
·
2,262 Posts
Can I ask where you want the date to go - in the cell that has been clicked ? ...or in the cell next to one that has been updated?
 

·
Registered
Joined
·
2,262 Posts
Alright, well whatever.

To enter the macro into the worksheet you are working in, go to the Tools menu, select 'Macro', then 'Visual Basic editor'.

Double-click the sheet in the 'VBA Project' section. Paste in the below:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Right(Target.Value, 10) <> Format(Now(), "DD/MM/YYYY") Then

If IsDate(Right(Target.Value, 10)) Then
Target.Value = Left(Target.Value, Len(Target.Value) - 10)
End If

Target.Value = Target.Value & " " & Format(Now(), "DD/MM/YYYY")

End If

End Sub



If you want it to appear in the cell-note (so that the date only appears when you hover over the cell it looks more like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Target.NoteText Format(Now(), "DD/MM/YYYY")

End Sub


Although that assumes you are not using the cell notes already and you have enabled the hover feature in Tools/Options/View 'Comment Indicator'.
 

·
2.8 HKS&#12473;&#12486;&#12483;&#12503;3 &#12289;
Joined
·
3,072 Posts
Discussion Starter #9
cool... I will give that a play tomorrow,,,, big thanks...
 

·
2.8 HKS&#12473;&#12486;&#12483;&#12503;3 &#12289;
Joined
·
3,072 Posts
Discussion Starter #10
Thats bloody brilliant, except it puts the date in every cell I click on... how do I get it to just work on clumn A ?
 

·
M's Factory
Joined
·
5,240 Posts
lol Quack you make me laugh.
 

·
Registered
Joined
·
2,262 Posts
AppleJack said:
Thats bloody brilliant, except it puts the date in every cell I click on... how do I get it to just work on clumn A ?
Sorry, welcome to IT - you never stated exactly what you want, so now it will cost extra to fix. :p

Been away for the bank hol weekend, will look at it tomorrow. so you something in, say, cell B50 and you want it to change the date in cell A50?

Can you send the spreadsheet or is it confidential? PM me if you want me to look at it.

Tim.
 
1 - 12 of 12 Posts
Top