Sticky: Watchrange, Concatenate date to existing cell value

[ClimoC]

Hey everyone.

This one is a little tricky

in cell B, data could be entered but isn't always.

BUT - If someone were to type in a cell in col B, then I need today's date to be concatenated on to the end of whatever they type in there. If there is nothing typed, I'd like it to stay blank completely. And just to make it tricky, it's set up as a watchrange. Please someone see if they can help me modify (or rewrite!) the below code to do this please.

Bonus points if we can have the date that is concatenated be a different colour from the text! (text = white, date = red)

P.S - Col A is blank, but cols C - BX are all full of various tables

If Target.Column = 2 Then
Dim b As Range
Dim z As Range
 

Set b = Range("Schedule!B6:B500")

    For Each z In b
        If z <> "" Then
        With z
        .Value = z.Value & "B2"
        End With
        
        End If
    Next
End If
End Sub

Thanks!

 

[ClimoC]

P.P.S - I used to have "Dim d As Date" in there before but couldn't get them to work so I put the formula "=TODAY()" in cell B2, which when this ran just put the 'word' "B2" on the end of every cell that wasn't blank - and as it was a watchrange, if you moved around the cells within col B, it kept adding "B2" everytime. The above seems to (for example if you just put the letter 'g' in a cell in Col B, pressed the down arrow 5 times, the cell you typed 'g' in would turn into): "gB2B2B2B2B2"

 

[yytsunamiyy]

little tip: writing sticky in front of your thread title isn't going to do anything at all - unless you are an admin...

more on point:

replace
.Value = z.Value & "B2"

with
.Value = z.Value & datevalue(now())

 

[ClimoC]

Oh ok.. didn't know that...


Thanks for the replacement code. Any idea how I can stop this from repeating itself everytime a cell is touched? Try copying and pasting that code into a work sheet (setting it as a watchrange macro obviously) and see how it just adds and adds and adds the new value on every time?

I need it to only do it if it's not already there... picky I know, but alas...

 

[Smitty]

See if this does what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
    '   Code goes in the Worksheet specific module
    Dim rng As Range
        '   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
        Set rng = Target.Parent.Range("B6:B500")
             '   Only look at single cell changes
            If Target.Count > 1 Then Exit Sub
            '   Only look at that range
            If Intersect(Target, rng) Is Nothing Then Exit Sub
            '   Action if Condition(s) are met (do your thing here...)
            Application.EnableEvents = False
                If LenB(Target.Value) > 0 Then Target.Value = Target.Value & " - " & Date
            Application.EnableEvents = True
End Sub

HTH,

 

[ClimoC]

Does anyone know if I can fix this problem by asking it to do (in laymans speak

"If you cannot find today's date somewhere in column B, then do the macro, otherwise if you do, then do nothing"

The reason I say specifically 'todays date' is because someone might type in Col B "Moved to 14/09/09" when today's date is the 3rd - so I'd like it to make that cell then say "Moved to 14/09/09 on 03/09/09"

Ergo, if the cell value were to say "Deleted", then it would change to "Deleted on 03/09/09"

I have already concatenated the " on" & "todays date" but want to know if the Find operation in vba will do this or if it's too tricky.

 

[ClimoC]

Hey Smitty - seems like this is closer, but it has the following problems:

1. The watchrange macro does not perform upon change of the cell (pressing enter once text has been typed), but only when you later highlight the cell that has text in it.

2. The problem of the macro running over and over again everytime you highlight a cell with a text value in it still exists.

Code now reads

If Target.Column = 2 Then
Dim changes As Range
        
        Set changes = Target.Parent.Range("B6:B500")
             
            If Target.Count > 1 Then Exit Sub
           
            If Intersect(Target, changes) Is Nothing Then Exit Sub
           
            Application.EnableEvents = False
                If LenB(Target.Value) > 0 Then Target.Value = Target.Value & "   on   " & DateValue(Now())
            Application.EnableEvents = True
                
End If
End Sub

 

[Smitty]

1. The watchrange macro does not perform upon change of the cell (pressing enter once text has been typed), but only when you later highlight the cell that has text in it.

If you're referring to the change event, it works for me.

2. The problem of the macro running over and over again everytime you highlight a cell with a text value in it still exists.

Yes, if you enter edit mode in a cell that already has a value in it, then the date would be appended as soon as the cell is exited and the change event fires. To prevent that you could use the InStr argument to see if there is a date in the target cell.

 

[ClimoC]

Ok, could I use the InStr to check for =TODAY()?

Reason being that as I stated initally, sometimes a date may be entered in the field already. It's a schedule changes column basically - so if someone moves an item to another day, in the column in question (B) they would type something like "Has been moved from the 14/06/09" and I am trying to concatenate =TODAY() onto the end of what they type so that users can see when this alteration was made. Therefor, making the cell in col B say "Has been moved from the 14/06/09 on 04/09/09" (using 04/09/09 as today's date)

So....yeah....?????

 

[Smitty]

Using TODAY() won't be accurate as a time stamp, because tomorrow it will change.

As for InStr, you can use that to see if there is a date in the cell already.