Adaptation of existing vba code to "understand" txt/date format


Dear Experts
I'm looking forward to receive your help to adapt some existing vba code or suggest another new solution:
The code below works fine and helps me to track changes made in the defined range.
However, in that range I'm using different formats. I have limited them to txt and date.
Depending on the cell changed the receiving cell must understand that this is txt or date format. Text format is ok as result but date is not shown in a readable format.
This formula would solve the issue but in this case I would need a helping column. Better would be of course if possible to add this to the existing coding.

For example =IF(ISNUMBER(E8)=FALSE;E8;TEXT(E8;"dd-mmm-yyyy"))

thank you
Nils

THIS IS THE CODE I'M CURRENTLY USING:

Dim vOld

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("A5:AE100")

    Sheets("Tracking").Protect Password:="123456", DrawingObjects:=True, _
        contents:=True, Scenarios:=True, _
        userinterfaceonly:=True
    Sheets("Tracking").EnableAutoFilter = True

    If Not Intersect(Target, rng) Is Nothing Then
    Debug.Print (Target.Column)
        Target.Offset(0, 32 - Target.Column).Value = Now()
        Target.Offset(0, 33 - Target.Column).Value = Environ("UserName")
    End If
   
'Check empty cell

If Intersect(Target, Range("A5:AE100")) Is Nothing Then Exit Sub

With Sheets("Tracking").Range("A" & Rows.Count).End(xlUp)(2)
    .Value = Now
    .Offset(, 1).Value = Environ("username")
    .Offset(, 2).Value = Target.Address
    .Offset(, 3).Value = vOld
    .Offset(, 4).Value = Target
End With

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Save old value
           If Not Intersect(Target, Range("A4:AE100")) Is Nothing Then vOld = Target
End Sub
Petersburg1Asked:
Who is Participating?
 
Arno KosterConnect With a Mentor Commented:
and for the events :
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Application.EnableEvents = False
Set rng = Range("A5:AE100")
[...]
If Intersect(Target, Range("A5:AE100")) Is Nothing Then GoTo finished
[...]
End With

finished:
Application.EnableEvents = True

end sub

Open in new window

0
 
Arno KosterCommented:
Firstly, to prevent future problems, please disable event handling inside of the onchange macro. If you change a cell value inside of this macro, it will call itself thereby an infinite loop could be created. Keep in mind that the events will have to be enabled again so the exit sub statement should be a goto finished one.

you then could use either something like :
    If IsDate(Target) Then
        .Offset(, 4).Value = CDate(Target)
    Else
        .Offset(, 4).Value = Target
    End If

Open in new window


or just
.Offset(, 4).Value = Target.Text

Open in new window




0
 
Petersburg1Author Commented:
perfect. thanks a lot
Nils
0
 
Arno KosterCommented:
you're welcome !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.