Changing VBA code from offset to another sheet

[zefrogi]

Hi, I have a VBA code which I found which works great for the purpose of tracking date and time of changes in each cell.

I'm wondering if I'm able to change the code to report on a set sheet rather than being an offset.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("B:F"), Target)
xOffsetColumn = 5
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Now
            Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

 

[rollis13]

Just to get you started, changing these few line of code will time-stamp in Sheet2 same cell range. Now you can work on it.

'...
For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
        Sheets(2).Range(Rng.Offset(0, xOffsetColumn).Address).Value = Now
        Sheets(2).Range(Rng.Offset(0, xOffsetColumn).Address).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
    Else
        Sheets(2).Range(Rng.Offset(0, xOffsetColumn).Address).ClearContents
    End If
Next
'...

 

[zefrogi]

Just to get you started, changing these few line of code will time-stamp in Sheet2 same cell range. Now you can work on it.

'...
For Each Rng In WorkRng
    If Not VBA.IsEmpty(Rng.Value) Then
        Sheets(2).Range(Rng.Offset(0, xOffsetColumn).Address).Value = Now
        Sheets(2).Range(Rng.Offset(0, xOffsetColumn).Address).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
    Else
        Sheets(2).Range(Rng.Offset(0, xOffsetColumn).Address).ClearContents
    End If
Next
'...

Thanks for helping! The code does work and puts the date and time on a new sheet but I sometimes get an error.

Run-time error '1004':

Method of 'Intersect' of object'_Global' failed

Debugging highlights: "Set WorkRng = Intersect(Application.ActiveSheet.Range("B:F"), Target)"

It seems to be when I'm editing a cell and try to click on a different sheet. Do you know any way around this or is it just a error to cope with?

 

[rollis13]

Unable to replicate the issue with the code shown in this thread, are you working on the sheet with the macro for the Worksheet_Change event ? do you have other macros in your project ?
It's not a 'great' solution but only to overcome the error, try adding an error check:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WorkRng As Range
    Dim Rng    As Range
    Dim xOffsetColumn As Integer
    On Error GoTo xit
    Set WorkRng = Intersect(Application.ActiveSheet.Range("B:F"), Target)
    xOffsetColumn = 5
    If Not WorkRng Is Nothing Then
        Application.EnableEvents = False
        For Each Rng In WorkRng
            If Not VBA.IsEmpty(Rng.Value) Then
                Sheets(2).Range(Rng.Offset(0, xOffsetColumn).Address).Value = Now
                Sheets(2).Range(Rng.Offset(0, xOffsetColumn).Address).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
            Else
                Sheets(2).Range(Rng.Offset(0, xOffsetColumn).Address).ClearContents
            End If
        Next
    End If
xit:
    Application.EnableEvents = True
End Sub

 

[zefrogi]

Unable to replicate the issue with the code shown in this thread, are you working on the sheet with the macro for the Worksheet_Change event ? do you have other macros in your project ?
It's not a 'great' solution but only to overcome the error, try adding an error check:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WorkRng As Range
    Dim Rng    As Range
    Dim xOffsetColumn As Integer
    On Error GoTo xit
    Set WorkRng = Intersect(Application.ActiveSheet.Range("B:F"), Target)
    xOffsetColumn = 5
    If Not WorkRng Is Nothing Then
        Application.EnableEvents = False
        For Each Rng In WorkRng
            If Not VBA.IsEmpty(Rng.Value) Then
                Sheets(2).Range(Rng.Offset(0, xOffsetColumn).Address).Value = Now
                Sheets(2).Range(Rng.Offset(0, xOffsetColumn).Address).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
            Else
                Sheets(2).Range(Rng.Offset(0, xOffsetColumn).Address).ClearContents
            End If
        Next
    End If
xit:
    Application.EnableEvents = True
End Sub

Aye, I've got another bunch of code above this but it's doing something completely different from this.

Thanks, seems to be working great. Still have the same kind of if I'm editing a cell and click off the timestamp doesn't appear but don't get the error that way at least!

 

[rollis13]

Is it possible to have a dummy file of your project ? just a couple of rows of data and no sensible data as long as the structure and macros of your workbook is maintained. A link to a file-hosting would be okay, maybe LINK free and no registration.

 

[zefrogi]

Is it possible to have a dummy file of your project ? just a couple of rows of data and no sensible data as long as the structure and macros of your workbook is maintained. A link to a file-hosting would be okay, maybe LINK free and no registration.

Actually can't from my work PC. Yah. I was just using a new workbook with my OG code and your edit.

It's just a case of if I'm in a cell editing, typing away and click on another sheet the the behaviour appears(either error or not recording timestamp). I think If I have to live with it, it might be better to have the error so people at least enter the information first.

 

[rollis13]

Managed to replicate your error. Changing sheet while inputing or editing a cell isn't the right way to use Excel when on macros; should always use Enter or Tab key elsewise there will be a high risk of consolidating wrong data. In this case this type of error will only trigger the "On Error" which will then exit the macro without time-stamping.
The only solution I see could be a warning message box instead of exiting but could become cumbersome.

 

[zefrogi]

Managed to replicate your error. Changing sheet while inputing or editing a cell isn't the right way to use Excel when on macros; should always use Enter or Tab key elsewise there will be a high risk of consolidating wrong data. In this case this type of error will only trigger the "On Error" which will then exit the macro without time-stamping.
The only solution I see could be a warning message box instead of exiting but could become cumbersome.

Aye, I hear you. Glad you managed to see what I was talking about. I don't think it's something that should be an issue - I hope. But you know what people are like when they start getting error codes.

Appreciate all your assistance though!

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.