Solved

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

Posted on 2011-09-19
4
336 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
  • 3
4 Comments
 
LVL 19

Expert Comment

by:akoster
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:
akoster 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:akoster
ID: 36579244
you're welcome !
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

830 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