Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-09-19
4
Medium Priority
?
348 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: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 2000 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

916 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