Petersburg1
asked on
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;
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
contents:=True, Scenarios:=True, _
userinterfaceonly:=True
Sheets("Tracking").EnableA
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("
.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(
'Save old value
If Not Intersect(Target, Range("A4:AE100")) Is Nothing Then vOld = Target
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
perfect. thanks a lot
Nils
Nils
you're welcome !
you then could use either something like :
Open in new window
or just
Open in new window