I have an .xlsm file with the following code I found through Experts-Exchange. I originally used =TODAY but that changed every time I opened the file. I need the date to be entered automatically into the cell and remain that date.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range
On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("A:A"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
With rCell.Offset(3, 18)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
I have a button in Access that when clicked transfers the data to a spreadsheet and saves it as a new Excel file. I don’t understand why the following code will work if I type something into a cell in column A but won’t if I transfer data to column A using transfer spreadsheet. When I transfer the text the information in the cells changes so why won’t it fill in the date? I typed the word “Date” in cell A1, in the .xlsm file, thinking it had to change from one text to another but that didn’t do anything either even though the word “Date” changed to “First Name”.
I have also tried a circular reference but that won’t work either.