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

Posted on 2011-09-19
Last Modified: 2012-05-12

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


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, _
    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
Question by:Petersburg1
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
LVL 19

Expert Comment

by:Arno Koster
ID: 36559964
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)
        .Offset(, 4).Value = Target
    End If

Open in new window

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

Open in new window

LVL 19

Accepted Solution

Arno Koster earned 500 total points
ID: 36559971
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

Application.EnableEvents = True

end sub

Open in new window


Author Closing Comment

ID: 36574356
perfect. thanks a lot
LVL 19

Expert Comment

by:Arno Koster
ID: 36579244
you're welcome !

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question