Two separate Time stamps


I have one that I can't seem to figure out. I'm attempting to have two separate columns record a time stamp using two separate data columns. I cant figure out the code. Example see file. Drop down is used in Column a, time stamp goes in column b. Then drop down is used in column L and time stamp goes into column M . This is all on one worksheet.

I got it figured out. 

Dim targetRng As Range

Dim rng As Range

Dim a, k As Integer

Set targetRng = Intersect(Application.ActiveSheet.Range("a:a, k:k"), Target)

a = 1

k = 1

 If Not targetRng Is Nothing Then

    Application.EnableEvents = False

      For Each rng In targetRng

        If Not VBA.IsEmpty(rng.Value) Then

           rng.Offset(0, a).Value = Now

           rng.Offset(0, k).Value = Now

           rng.Offset(0, a).NumberFormat = "mm-dd-yy, hh:mm AM/PM"

           rng.Offset(0, k).NumberFormat = "mm-dd-yy, hh:mm AM/PM"


           rng.Offset(, a).ClearContents

           rng.Offset(, k).ClearContents

         End If


    Application.EnableEvents = True

  End If



Hi Ben.
You say "I got it figured out" so don't you need help on the two date issue? (I  tried your file just entries in A or L produce dates- are you trying to force the use to choose a Lead Status in L once A is changed?). If so, please edit your question (on maps?) to change the subject to reflect your question. If not, please edit to clarify your question.

On your Googe Maps link question, what information do you know already? Town, location name, address, zip code (or postcode in other parts of the world)?

Also, street addresses aren't always that precise so (if people have to go to site), have you considered more accurate location indicators, say what3words which has the entire globe split down into 3 metre squares (each described by a unique combination of three words)? I'm not sure if VBA could retrieve those yet though!

Note for future questions in the forum, if you're showing VBA code, kindly click on the Code button and put it within the area bounded by the square brackets ... ]Code_Goes_Here[...
John_Ru (rep: 782) Jan 4, '21 at 3:16 am
Hi Ben

Looks like you've updated your question but did my file work for you? (If so kindly select the answer, if not please add a question to the discussion on that).

Don't forget to ask you Google Maps question. In the event that I can't sort it, I'm sure others can.
John_Ru (rep: 782) Jan 4, '21 at 8:23 am
Sorry, I deleted an unused cell so now my target cells are b and l which auto populate when drop downs are used in a and k. Seems to be working fine on multiple systems. I have added the new file for your review. I will probably leave good enough alone, but if you have a better way that works exactly the same. I'll consider it.

On mapping, I have the business name, address, city, state, zip. I want to add the mapping link to eventually add a routing feature. It isn't too important at this time. 

Again thanks for your help.
Ben B (rep: 2) Jan 4, '21 at 8:34 am

While your code in the new file BNB Signs1.xlsm works (and the method seems to suit you better than my shorter code), you have unnecessary bits in Sub Worksheet_Change... 

E.g. you set a=1 and k=1 for both Offset(0,a) and Offset (0,k). They are then effectively the same and equivalent to Offset(0,1). Try commenting out the lines in bold as follows and you'll see it still works: 
If Not VBA.IsEmpty(rng.Value) Then
           rng.Offset(0, a).Value = Now
           'rng.Offset(0, k).Value = Now
           rng.Offset(0, a).NumberFormat = "mm-dd-yy, hh:mm AM/PM"
           'rng.Offset(0, k).NumberFormat = "mm-dd-yy, hh:mm AM/PM"
You can then delete those lines (and make your code ever so slightly faster!)

Thanks for selecting my answer.
John_Ru (rep: 782) Jan 4, '21 at 10:04 am
Forgot to say, what do you expect to achieve from the associated Else statement (again with lines giving a "duplicated effect")?

Feel free to ask the mapping question when you're ready for that- i'm sure someone will havea good answer. 

Good luck.
John_Ru (rep: 782) Jan 4, '21 at 10:11 am
p.s. I've since worked out a solution for the mapping thing but you might get better answers if you pose a detailed question.
John_Ru (rep: 782) Jan 4, '21 at 3:23 pm
Add to Discussion


Selected Answer

Hi Ben

If you want to add dates for entries made separately in columns A and K, you just need the code below (included in the revised file for you to try):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim targetRng As Range

Set targetRng = Union(Columns(1), Columns(12))

If Intersect(targetRng, Target) Is Nothing Then Exit Sub

Application.EnableEvents = False
        Target.Offset(rowOffset:=0, columnOffset:=1).Value = Now
        Target.Offset(rowOffset:=0, columnOffset:=1).NumberFormat = "mm-dd-yy, hh:mm AM/PM"
Application.EnableEvents = True

End Sub

The bit Union(Columns(1), Columns(12)) means columns A and K so if the changed cell isn't there, I quit the sub (as Don does in the tutorial I think you probably followed).

For some very odd reason, Target.Offset(0,1).Value didn't work as normal but the more verbose Target.Offset(rowOffset:=0, columnOffset:=1) did. Suggest you try the file as is then change the two line to the more normal Target.Offset(0,1) and check that works. 

Your question on Google Maps is a separate subject really (and needs the details requested in my discussion point) so I suggest you edit this question to delete that bit and provide more details in a separate question.

Hope this helps


Answer the Question

You must create an account to use the forum. Create an Account or Login