Solved

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

Posted on 2011-09-19
4
341 Views
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
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
0
Comment
Question by:Petersburg1
[X]
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
4 Comments
 
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)
    Else
        .Offset(, 4).Value = Target
    End If

Open in new window


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

Open in new window




0
 
LVL 19

Accepted Solution

by:
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

finished:
Application.EnableEvents = True

end sub

Open in new window

0
 

Author Closing Comment

by:Petersburg1
ID: 36574356
perfect. thanks a lot
Nils
0
 
LVL 19

Expert Comment

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

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

617 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