Macro to set current time in a Cell without changing similar values in other cells

[rahulndeepti]

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

Even assigning value to variable does not help:

Sub Timenow()
' Timenow Macro
' Keyboard Shortcut: Ctrl+b
Dim TN As String
TN = Format("=now()", "h:mm")
ActiveCell.Value = TN
End Sub
Can someone tell me how to restrict the macro to change the value of current/active cell only without effecting other cell values?

 

[jproffer]

Your code above is just putting the formula into the cell, which will update every time the sheet is calculated.

try this:

Sub test2()
    ActiveCell.Value = Time
End Sub

 

[rahulndeepti]

Not understood, but I am using Win 7 Home Basic. Can I send an attachment in this?

 

[jproffer]

This board doesn't accept attachments. In place of this:

Sub Timenow()
'
' Timenow Macro
' Keyboard Shortcut: Ctrl+b
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.NumberFormat = "h:mm"
Range("F5").Select
End Sub

put this:

Sub Timenow()
'
' Timenow Macro
' Keyboard Shortcut: Ctrl+b
    ActiveCell.Value = Time
End Sub

 

[rahulndeepti]

Wow! that helps!!! Thanks a ton!