How can I let a Macro in Excel set current time in a Cell without changing similar values in other cells?

Question

I have a Macro 'Timenow' to give current time in a Cell of MS Excel, but then it changes all Cell values of sheet where the macro was used.
Sub Timenow()
'
' Timenow Macro
' Keyboard Shortcut: Ctrl+b
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.NumberFormat = "h:mm"
Range("F5").Select
End Sub

Can someone tell me how to restrict the macro to change the value of current cell only?

Answer 1

Not exactly sure about your question, but I think it relates to wishing to set a time value that does not change, once entered.



The 'Now()' function is volatile, which means it changes on every WorkSheet_Calculate event. So, if you wish to set a time in the activecell that is non-volatile, you can use an event handler such as:



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

ActiveCell.Value = Time

ActiveCell.NumberFormat = "h:mm"

End Sub



Edit: I assume you are conversant with event handlers placed in the worksheet code modules.

Answer 2

Why not just have a macro to set the time? You could have the time set like you do now then copy the cell and paste special, values so that the time is non-volitile (if you don't want it to change).



I am not a VBA programmer so take that for what it is worth.

Answer 3

i don't think of you desire a macro for this. You the two want Excel formulation in cells B2 to E2 that references records on your different sheets. or you will possibly could use something like Excel's purpose seek for features. examine it out in Excel help. looking on what you're doing, you will possibly desire a macro. yet you have particularly not given us sufficient records to truly be waiting that may well be useful you because of the fact there isn't something asserting how b2 by using e2 interrelate to one yet another or how they are figured. so some distance as A1, you may hyperlink the linked fee of that cellular to the linked fee on your different workbook. That chart fee for A1 ought to be referencing a cellular fee someplace for its records to create the chart. you may reference a similar cellular fee that the Chart does, not the chart itself. i might evade macros in case you could. You already look perplexed and that i think of macros might have you ever each and all of the greater perplexed because of the fact it takes a on a similar time as to study the thank you to good application them.

Answer 4

The Ctrl+Shift+Semicolon hotkey will place just the current time value in the cell. (Ctrl+Colon will enter the current date.)



As others have said, NOW() is volatile, and always changes when the sheet is recalculated.