VBA multi timestamp option?

whodey33

New Member
Joined
Aug 31, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello people way more skilled than this guy.

I'm trying to create a bit of a call/issue tracker making it as simple as possible for those taking the calls to complete and get me useful data. I'm totally a google stealing machine and don't know much but when it works it works.

So I found a VBA formula that looks at lets say column A and if it has a # it will auto populate B (e.g. A1 has 1, B1 gets static timestamp)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
With Target.Offset(0, 1)
.Value = Now
.NumberFormat = "MM/DD/YYYY hh:mm AM/PM"
End With
End Sub

This seems to work perfectly however I also want to use it for column F and G. Column F has a data validation list for in progress/completed options. When that option is selected, updated, I want column G to get the timestamp. That works if I just use:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 6 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
With Target.Offset(0, 1)
.Value = Now
.NumberFormat = "MM/DD/YYYY hh:mm AM/PM"
End With
End Sub

I've been able to do one or the other but not sure how to do both. Is there a specific statement I need to add to run the first and then the second? Any help would be glorious! If anyone knows of another way that would be super....I was trying functions but the date auto updated which didn't want. Looking down the road, I want to be able to see the time the call/issue came in to the time resolved.

Thank you for any help!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,150
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

This will handle both columns:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If (Target.Column = 1) Or (Target.Column = 6) Then
    With Target.Offset(0, 1)
        .Value = Now
        .NumberFormat = "MM/DD/YYYY hh:mm AM/PM"
    End With
End If

End Sub
 
Solution

whodey33

New Member
Joined
Aug 31, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Well thank you good sir...I knew it was going to be something simple! Works perfectly!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,150
Office Version
  1. 365
Platform
  1. Windows
You are welcome!
Glad I was able to help.
:)
 

Forum statistics

Threads
1,148,346
Messages
5,746,198
Members
423,998
Latest member
Nancy2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top