Forum Discussion
Automatically adding a timestamp after a pulldown is selected
I have been trying to research this myself and I thought I found the solution, but I am still stuck. I have a spreadsheet that has several columns with pull down menus for example Column P. Next to each of these columns is a blank column (Column Q) that I want to grab the date and time when the first column selection is changed (column P).
I am using this code which I adapted the range from the original post.
Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("P2:P670")) Is Nothing Then
Target.Offset(1, 0) = Now
End If
End Sub
If I make a new blank spreadsheet, I can make the code work, but when I try to put it either directly into the worksheet, or in a module on the worksheet, nothing happens when I change the value of column P by selecting the pull down. No date appears on column Q.
The goal is for it to record the day and time whenever that first column has something different selected from the pull down.
Any ideas?
7 Replies
The code should be in the worksheet module. You can open this module from Excel by right-clicking the sheet tab, then selecting 'View Code' from the context menu, or by double-clicking the sheet in the Project Explorer pane of the Visual Basic Editor.
I'd change the code to look like this:
Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("P2:P670")) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False Intersect(Target, Range("P2:P670")).Offset(1, 0).Value = Now Application.EnableEvents = True Application.ScreenUpdating = True End If End SubThanks for the advice, but I am still missing something. I have your code in the correct worksheet, vs the module or workbook. (See photo) But, when I select something from the pull down menu in Column P, I still get nothing in the next column (See [photo)
What am I missing? Does it have something to do with the pull down menu's?
Since you didn't include the column headers in the screenshot, I cannot see which cells are involved.
