Insert date, but keep static on archived

[paankadu]

How do I get the date to remain static when a sheet is saved and not change
to the current date when it is re opened. It is a daily log and each shift
it will be opened to update. I would like the date to autopopulate the
current date but when it is saved it needs to remain that date. Each shift
will be saving the file under a new name. When they reopen the master it
needs to put the next current date in.

The users won't know to do the ctrl keys to make the date static. I need to
make it as automated as I can.

Thanks in advance for your assistance.

 

[Jacob Skaria]

You can using the Workbook Open event. Set the security level to low/medium
in (Tools|Macro|Security). From workbook press Alt+F11 to launch VBE (Visual
Basic Editor). From the left treeview search for the workbook name and click
on + to expand it. Within that you should see the following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

Double click 'This WorkBook' and paste the below code to the right code pane.

Private Sub Workbook_Open()
Worksheets("Sheet1").Range("A1") = Date
End Sub

If this post helps click Yes

 

[paankadu]

Thank you so much. So when this is saved (as a file save as ....) it will
retain the date that is in there currently (for archival purposes), but when
a new instance is opened it will change to the current date? I got the code
in and it changed to today's date. I just need to be sure it will retain
that date when the user does his file save as... Also, this will work if
the cell is locked and protected or does it need additional code?

I have learned so much from people like you on this forum, and am still
learning Thank you for your willingness to share your expertise!

 

[Jacob Skaria]

--The date will only change during Open event.
--If the sheet is protected then try.. Password is not mandatory...

Private Sub Workbook_Open()
With Me.Sheets("sheet1")
..Unprotect Password:="password"
..Range("A1") = Date
..Protect Password:="password"
End With
End Sub

If this post helps click Yes

 

[paankadu]

OK, one more question...
How do I get this to work on 2 worksheets? I have one sheet named "Day Log"
and another sheet named "Night Log". Also, I have a print button on each of
the sheets and the coding below is used to print the day log. How do I add
coding for the same type of button to work on the night log?




Option Explicit

Sub Hide_Print_Unhide()
ActiveSheet.Unprotect Password:="mypassword"
Dim rw As Long
Dim rng As Range
Dim cell As Range

Application.ScreenUpdating = False

Set rng = Sheets("Day Log").Range("A7:b23")

With rng.Columns(1)
For Each cell In rng
If Application.WorksheetFunction.CountA( _
.Parent.Cells(cell.Row, 1).Range("A1:g1")) = 0 Then _
.Parent.Rows(cell.Row).Hidden = True
Next cell
.Parent.PrintOut
.EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
ActiveSheet.Protect Password:="mypassword"
End Sub



Thanks again!
Thanks,
Angie