CODEWITHSUNDEEP
vbaexcel
AlexB
AlexB

Reputation: 2174

Populate cell with the current date when spreadsheet is opened VBA

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

Upvotes: 4

Views: 43201

Answers (1)

Mike
Mike

Reputation: 120

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!

Upvotes: 7

Related Questions