Date & Time Formula

[blandreth]

I'm looking for a simple excel formula that will populate a cell with the date and time when the file is opened.

I appreciate the feedback.

Brian

 

[blandreth]

Thanks.

I am not good at writing code. I can modify to meet my needs if I get a starting point. So the code would look like this:

Sub SaveFile()
'
' SAVEFILE Macro
'Dim sNameSheet As String

sNameSheet = Range("NAME").Value

Sheets(“report”).Range(“G13”).Value=Now()

ActiveWorkbook.SaveAs Filename:="C:\Users\Brian.landreth\Documents\Weld Reports\2019 Weld Reports" & sNameSheet

'
End Sub

 

[Joe4]

Yes, that is what I am talking about (it isn't writing any code - just dropping the code I gave you in there).
Just try to think of it in logical steps. If you want the date/time stamp to be part of the file you are saving, that needs to happen BEFORE the file is saved.
The lines of code in VBA run in the order they are found in.

 

[blandreth]

Awesome!!

Thanks for your help...again \m/

 

[blandreth]

Ok, I copied and pasted the line of code and it is now getting hung up at this line. Not sure what I'm doing wrong.

 

[Joe4]

Do you really have slanted quote marks here?

[COLOR=#333333]Sheets(“report”).Range(“G13”).Value=Now()[/COLOR]

VBA does not like those. Replace them with the straight up and down ones, like in my original post (").

[COLOR=#333333]Sheets("report").Range("G13").Value=Now()[/COLOR]

 

[blandreth]

That was it. When I copied it in, it must have put the slanted quote marks in.

Thanks again for all of your help. I greatly appreciate it!!!

 

[Joe4]

You are welcome.

 

[blandreth]

Sorry, but I found one more thing I have a question about. If I protect the cell to keep someone from changing the date formula it stops the code from working. If the cell is not locked the code works perfect. What can I do to make the code work with the cell locked.

Thanks!

 

[Joe4]

You need to unlock it before updating the cell, and then relock it afterwards.

So the structure will look like:

Sub SaveFile()
'
' SAVEFILE Macro
'Dim sNameSheet As String


sNameSheet = Range("NAME").Value


ActiveSheet.Unprotect "password", True, True
Sheets("report").Range("G13").Value = Now()
ActiveSheet.Protect "password"


ActiveWorkbook.SaveAs Filename:="C:\Users\Brian.landreth\Documents\Weld Reports\2019 Weld Reports" & sNameSheet


'
End Sub

Make sure to replace "password" with your actual password.

 

[blandreth]

It's getting hung up on the first line: ActiveSheet.Unprotect "password", True, True

Also, I know to protect the sheet it ask for a password and then confirmation of the password. Not sure if that makes a difference with the code