VBA Multiple Timestamps in One Worksheet

[wmunsey81]

Hello,

New to this forum and could not really find an answer for some reason related to this, but hopefully someone could help on this one. I am sure it is an easy solution, but I am not getting any luck.

I am trying to get these two codes to work simultaneously but only work when when they are selected:

Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("E:E"), Target)
xOffsetColumn = -2
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 = Date
            Rng.Offset(0, xOffsetColumn).NumberFormat = "mm/dd/yyyy"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

And this one...:

Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
xOffsetColumn = 12
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 = Date
            Rng.Offset(0, xOffsetColumn).NumberFormat = "mm/dd/yyyy"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

I have tried a variable of deleting certain lines and sometimes it works, but only one of the time stamps work and then the other one doesn't work. Any advice would be helpful.

Thank you,
-Bill

 

[CalcSux78]

You can only have one Worksheet_Change event per worksheet... You can combine the code so it looks for changes in either column to trigger that section of code.

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=#008000]'Update 20140722[/COLOR]
Dim WorkRng[B][COLOR=#ff0000]A[/COLOR][/B] As Range
[COLOR=#0000ff]Dim WorkRngB As Range[/COLOR]
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng[B][COLOR=#ff0000]A[/COLOR] [/B]= Intersect(Application.ActiveSheet.Range("E:E"), Target)
[COLOR=#0000ff]Set WorkRngB = Intersect(Application.ActiveSheet.Range("B:B"), Target)[/COLOR]


xOffsetColumn = -2
[COLOR=#ff0000]Application.EnableEvents = False[/COLOR]
If Not WorkRng Is Nothing Then
    For Each Rng In WorkRng[B][COLOR=#ff0000]A[/COLOR][/B]
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Date
            Rng.Offset(0, xOffsetColumn).NumberFormat = "mm/dd/yyyy"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
End If


[COLOR=#008000]'repeat for 2nd range[/COLOR]
[COLOR=#0000ff]xOffsetColumn = 12[/COLOR]
[COLOR=#0000ff]If Not WorkRngB Is Nothing Then[/COLOR]
[COLOR=#0000ff]    For Each Rng In WorkRngB[/COLOR]
[COLOR=#0000ff]        If Not VBA.IsEmpty(Rng.Value) Then[/COLOR]
[COLOR=#0000ff]            Rng.Offset(0, xOffsetColumn).Value = Date[/COLOR]
[COLOR=#0000ff]            Rng.Offset(0, xOffsetColumn).NumberFormat = "mm/dd/yyyy"[/COLOR]
[COLOR=#0000ff]        Else[/COLOR]
[COLOR=#0000ff]            Rng.Offset(0, xOffsetColumn).ClearContents[/COLOR]
[COLOR=#0000ff]        End If[/COLOR]
[COLOR=#0000ff]    Next[/COLOR]
[COLOR=#0000ff]End If[/COLOR]
[COLOR=#ff0000]Application.EnableEvents = True[/COLOR]
End Sub

Changes are colored.

 

[wmunsey81]

Thank you for the input. When running, however, it says at the line after the red line of "Application.EnableEvents = False", I get a bug at the line that says "If Not WorkRng Is Nothing Then" giving me an "Object Required" error. Any idea why?

 

[wmunsey81]

I guess I should explain what I am doing...

In column E, when I enter something in that, I want it to enter a timestamp in column C. Column B is a Y or N and when changed, I want it to timestamp column N. Hopefully that helps in explaining why I am doing what I am doing.

 

[CalcSux78]

I missed one change... We don't have a WorkRng declared... Change it to WorkRngA

 

[wmunsey81]

I missed one change... We don't have a WorkRng declared... Change it to WorkRngA

Yep... worked perfectly now. Thank you so much!!!