Trying to get Now() in cell C18 of sheet named HSheet using VBA.

[omairhe]

Hello all,

How to get a timestamp whenever I save the file using the Now() function in my sheet named HSheet of cell C18. I tried the following code and it worked only once. How do I get a new timestamp each time I save my excel file.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim LValue As Date
LValue = Now
Worksheets("HSheet").Range("C18").Value = LValue
End Sub

Thanks and will appreciate.

 

[Fluff]

That's exactly what your code does. What makes you think it doesn't work?

 

[GlennUK]

If it only worked once, you need to try to find out what is preventing that event from firing. What other VBA code do you have in the book?

 

[MARK858]

If you run the code below does it run now?

Sub xxxxNow()
   Application.EnableEvents = True
End Sub

 

[omairhe]

That's exactly what your code does. What makes you think it doesn't work?

It worked just once out of the blue and now it doesn't work. The cell stays blank and the workbook gets saved without any error messages. Now I try the following and not even getting a Message Box.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox ok
End Sub

 

[Fluff]

In that case try running the code Mark posted in post#4 & then save your workbook again.

 

[omairhe]

Ok I learned something new now. When I close and re-open the excel file I can run the original code successfully. However for the second time save. This will not work. Even other vba codes are not working after first time saving the file.

 

[omairhe]

In that case try running the code Mark posted in post#4 & then save your workbook again.

Yes that fixes it. Works like magic. Thanks so much for pointing that out to me.

 

[omairhe]

If you run the code below does it run now?

Sub xxxxNow()
   Application.EnableEvents = True
End Sub

Thanks Mark!

 

[MARK858]

@omairhe, you're welcome but as @GlennUK stated you need to check your other codes, find the one where it sets enableevents to false and see why it isn't setting it back to True either by design or the code erring out.