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

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,015
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
That's exactly what your code does. What makes you think it doesn't work?
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,034
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
If you run the code below does it run now?

VBA Code:
Sub xxxxNow()
   Application.EnableEvents = True
End Sub
 
Solution

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,015
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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.

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
In that case try running the code Mark posted in post#4 & then save your workbook again.
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,015
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 26, 2009
Messages
2,015
Office Version
  1. 2019
Platform
  1. Windows
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.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,034
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
@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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,262
Messages
5,635,140
Members
416,843
Latest member
mrbrown91b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top