Populate cell with the current date when spreadsheet is opened VBA

Question

How can I insert today's date in the cell when spreadsheet opens, so that if someone wants to change it then they can do it by changing it right within the cell.

I have tried the following, but without much luck

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim temp As String

    If Home.Range("_invoiceDate").Value = "" Then
        Home.Range("_invoiceDate").Value = Date
    End If
End Sub  

The cell is a named range "_invoiceDate" and worksheet is "Home"

Thanks for your help in advance

Answer 1

For the code to run when you open the workbook, you will need to place the code in the Workbook sheet like this.

Private Sub Workbook_Open()
    dim Home as Worksheet
    set Home = Worksheets("Home")
    Home.Range("_invoiceDate").Value = Format(Now(),"mm/dd/yyyy")  
End Sub

I believe this is what you are looking for.

I tested this for switching between worksheets and it works fine.

Private Sub Worksheet_Activate()

Dim Home As Worksheet
Set Home = Worksheets("Home")
Home.Range("_invoiceDate").Value = Format(Now(), "mm/dd/yyyy")

End Sub

Good luck and happy coding!