Add date to worksheet when userform is closed

[ipbr21054]

My cell in column P is selected & a userform then pops up.
The invoice number shown will be entered in the active cell of which is in column P, as per photo attached.

The code on the button TRANSFER INV NUMBER TO DATABASE is as follows.
When the user runs the code not only will the inv number be added to my worksheet BUT i now also wish to add the todays date in column M
Would the code in Red below be correct ?

Private Sub TransferInvNumber_Click()
    
    ActiveCell.Value = TransferInvoiceNumber.TextBox1.Value
    ActiveCell.Offset(-3) = "Date"
    .NumberFormat = "dd/mm/yyyy"
      Unload Me
      
    Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\"
    If ActiveCell.Column = Columns("P").Column Then
    If Dir(FILE_PATH & ActiveCell.Value & ".pdf") <> "" Then
      ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".pdf"
    Else
      ActiveCell.Hyperlinks.Delete
      MsgBox (FILE_PATH & ActiveCell.Value & ".pdf" & vbNewLine & vbNewLine & "FILE IS NOT IN FOLDER SPECIFIED, PLEASE CHECK PATH IS CORRECT"), vbCritical
    End If
    Else
      MsgBox "PLEASE SELECT AN INVOICE NUMBER.", vbExclamation, "HYPERLINKING THE INVOICE NUMBER"
    End If

End Sub

 

[6StringJazzer]

That code will add the date when the button is clicked, according to the question in your post, if the active cell is in column P. However, your code to set the date occurs before your test to see whether the active cell is in column P. You could verify that simply by testing it.

However, your title says "when userform is closed." It will not add a date when the UserForm is closed.

Maybe you can clarify just what you need.

 

[ipbr21054]

Basically when the invoice number is added to the cell in coming P I just want the date added to the cell in column M

 

[ipbr21054]

With this in use i get an eror message "Compile error Invalid or unqualified referance"

Private Sub TransferInvNumber_Click()
    
    ActiveCell.Value = TransferInvoiceNumber.TextBox1.Value
    ActiveCell.Offset(-3) = "Date"
    [COLOR=rgb(184, 49, 47)].NumberFormat = "dd/mm/yyyy"[/COLOR]
      Unload Me
      
    Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\"
    If ActiveCell.Column = Columns("P").Column Then
    If Dir(FILE_PATH & ActiveCell.Value & ".pdf") <> "" Then
      ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".pdf"
    Else
      ActiveCell.Hyperlinks.Delete
      MsgBox (FILE_PATH & ActiveCell.Value & ".pdf" & vbNewLine & vbNewLine & "FILE IS NOT IN FOLDER SPECIFIED, PLEASE CHECK PATH IS CORRECT"), vbCritical
    End If
    Else
      MsgBox "PLEASE SELECT AN INVOICE NUMBER.", vbExclamation, "HYPERLINKING THE INVOICE NUMBER"
    End If

End Sub

 

[ipbr21054]

This worked but was placed in the wrong cell

ActiveCell.Offset(-3) = Format(Date, "dd-mmm-yyyy")

My invoice number was placed in cell P6 which is correct
The date was placed in cell P3 which is in correct.

I thought in my code if i wrote -3 it would be 3 cells to the left of cell P but i went up the page ?

 

[ipbr21054]

This worked for me

ActiveCell.Offset(0, -3) = Format(Date, "dd-mmm-yyyy")