timestamping cell changes.. 4

[marduk813]

I've been toying with this for some time now, and I haven't been able to come up with a working model. I have 3 columns of data in an Excel worksheet (C, D, & G). Column C only has one changeable cell (C7) and the rest are changed by formulas. Column D can be changed from rows 8 to 29, and G can be changed from rows 7 to 29. Basically, the first editable cell in row 7 is in column C instead of D. (Don't ask me why, I didn't design the worksheet)
Now...whenever a value is entered into any of those editable cells in the range defined above, I need to insert the current system time into a cell on the same row, but in either column B or E (depending on which column the change was made).
So, if any cell in the range of C7-D29 is changed, I need to timestamp the cell in column B of that same row. If any cell in the range of G7-G29 is changed, I need to timestamp the cell in column E of that same row.
I've been trying to use the Worksheet_Change event since it provides the range of the cell that was changed, but maybe that's not the right one to use? I'm also updating the source data for a chart using the Worksheet_Change event, and I was thinking that might be having an effect on my timestamp results (extra cells are being timestamped).

Any thoughts on how to go about this?

 

[marduk813]

Well, as much as I hate replying to my own posts, please ignore the above question as I now have everything working correctly. Thanks in advance to anyone who would've responded.


Jas

 

[SBendBuckeye]

Hello Jas,

Would you mind posting your solution? I was just getting ready to post a similar request when I saw yours. Perhaps it will solve my problem as well.

Have a great day!

 

[marduk813]

Sure Buckeye. Here's what I did:

Private Sub Worksheet_Change(ByVal Target As Range)
' this sub adds a timestamp on the row where data was entered

    Dim intRow, intCol, intTimeCol As Integer   ' target row and column, and timestamp column

    ' check to see if target cell is in the input portion of the worksheet
    If (Target.Row >= 7) And (Target.Row <= 29) Then
        ' check to see if target column is C, D, or G
        If (Target.Column = 3) Or (Target.Column = 4) Or (Target.Column = 7) Then
            Select Case Target.Column
                Case 3: intTimeCol = 2  ' set timestamp column to B
                Case 4: intTimeCol = 2  ' set timestamp column to B
                Case 7: intTimeCol = 6  ' set timestamp column to F
            End Select
            
            If Target.Text <> &quot;&quot; Then   ' check to see if the target cell is empty
                Cells(Target.Row, intTimeCol).Value = Time  ' add timestamp to target row
            Else
                Cells(Target.Row, intTimeCol).Value = &quot;&quot;    ' delete timestamp if target cell is empty
            End If
        End If
    End If

End Sub

This code is working great for me. When you delete the data from the target cell, it deletes the timestamp as well. Hope this helps.

Jas

 

[SBendBuckeye]

Thanks, that was very helpful!

Have a great day!

 

[tweek312]

Could anyone help me with this bit of VBA. I am not very familiar but could use this code for a project of mine.

Thanks! -MR

 

[SBendBuckeye]

Hello TweekX,

1. When you press Enter or use an arrow key to exit a cell in which you were entering/editing data, Excel raises a change event as you see below, you cannot change the header.

Private Sub Worksheet_Change(ByVal Target As Range)

Target is the range of cells just changed and could be multiple cells (eg you clicked and held and selected a block of cells and then hit your delete key to clear the data contained in the selected area). If you wanted to restrict the code to only one cell ranges you can check to see if Target.Cells.Count = 1

2. Code checks to ensure data is in rows 7 - 29 inclusive
If (Target.Row >= 7) And (Target.Row <= 29) Then

3. Code checks to see if in given column - Col A = 1
' check to see if target column is C, D, or G
If (Target.Column = 3) Or (Target.Column = 4) Or (Target.Column = 7) Then

4. Set column variable based on Target.Column

Select Case Target.Column
Case 3: intTimeCol = 2 ' set timestamp column to B
Case 4: intTimeCol = 2 ' set timestamp column to B
Case 7: intTimeCol = 6 ' set timestamp column to F
End Select

5. If target cell contains data then set timestamp column

'Check to see if the target cell is empty
If Target.Text <> "" Then
'Add timestamp to target row
Cells(Target.Row, intTimeCol).Value = Time
Else
'Delete timestamp if target cell is empty
Cells(Target.Row, intTimeCol).Value = ""
End If
End If
End If

Please post again if you have more questions. Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[tweek312]

I have made it work in part however when I do an autofill the added values do not change the intTimeCol. Any suggestions?

 

[TonyJollans]

Hi tweek312,

The reason for your problem is that the code just checks the first changed cell. When multiple cells are changed (as in autofill) the code must loop through all of them. To do this simply, declare a new range variable, TargetCell; change all references to Target to TargetCell, and add a loop round it all ..

[blue][red]Dim TargetCell As Range[/red]
    [green]' check to see if target cell is in the input portion of the worksheet[/green]
[red]For Each TargetCell In Target[/red]
    If ([red]TargetCell[/red].Row >= 7) And ([red]TargetCell[/red].Row <= 29) Then
        [green]' check to see if target column is C, D, or G[/green]
        If ([red]TargetCell[/red].Column = 3) Or ([red]TargetCell[/red].Column = 4) Or ([red]TargetCell[/red].Column = 7) Then
            Select Case [red]TargetCell[/red].Column
                Case 3: intTimeCol = 2  [green]' set timestamp column to B[/green]
                Case 4: intTimeCol = 2  [green]' set timestamp column to B[/green]
                Case 7: intTimeCol = 6  [green]' set timestamp column to F[/green]
            End Select
            
            If [red]TargetCell[/red].Text <> "" Then   [green]' check to see if the target cell is empty[/green]
                Cells([red]TargetCell[/red].Row, intTimeCol).Value = Time  [green]' add timestamp to target row[/green]
            Else
                Cells([red]TargetCell[/red].Row, intTimeCol).Value = ""    [green]' delete timestamp if target cell is empty[/green]
            End If
        End If
    End If
[red]Next[/red][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[tweek312]

Thanks!

 

[tweek312]

There are a couple of things that come into play here. Please accept my apologies!

1). My code had a bug in it - Sub name should be Workbook_SheetChange

2). As written, even after the change, the code would only handle a since target cell (eg when you typed in a value in one of the cells or you copied from 1 cell into another cell. But it does not work if you use the fill handle to drag down through a series of cells

3). Changing the timestamp value in Col B causes the code to be called again for Col B.

Attached is a fully working test version with Debug.Print statements embedded in the code to help you see what is happening. To comment them out, just prefix them with a single quote '. I put Mike on those lines as a comment to help you find them.

Here are the changes that resolve the above issues.

1). Fix the sub procedure name - aaaaaaaarrrrrrrrrrrrrrrhhhhhhhhhhhhhhhhggggggggggggggg.

2.) Move your code down into a separate sub routine and check how many cells are being passed in the targer range. If it is one, call the sub with Target. If it is more than 1, then spin through the target cells collection, calling your new sub once for each cell in the collection. The target.cells collection is itself just more ranges, that is why you can pass Target into your sub as well as the individual Target.Cells - at that point they are 1 cell ranges which the code can handle.

3). Turn off Application.DoEvents so changes to timestamp columns do not cause another call. You would not believe some of my early, ugly code where I set flags to trap this situation. ALWAYS make sure you turn it back on before you are done or it will remain off.

This code is posted on behalf of Gary Winey

Thanks Gary! You're the best!

Gary is at j2associates@yahoo.com

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)
' this sub adds a timestamp on the row where data was entered
Dim OneCell As Range
    Application.EnableEvents = False
    If target.Cells.Count = 1 Then
        Call ValidateEntry(target)
    Else
        Debug.Print "Multiple Cells: " & target.Cells.Count 'Mike
        For Each OneCell In target.Cells
            Call ValidateEntry(OneCell)
        Next OneCell
    End If
    Set OneCell = Nothing
    Application.EnableEvents = True
End Sub

Private Sub ValidateEntry(ByVal target As Range)
    Dim intRow, intCol, intTimeCol As Integer   ' target row and column, and timestamp column

    'Comment out the Debug.Print statement by putting a single quote at the beginning of the line
    Debug.Print target.Address; "  Len: " & Len(target.Value); "  Value: '" & target.Value & "'"; "  Text: '" & target.Text & "'"; "  Col: " & Chr$(target.Column + Asc("A") - 1) & " or " & target.Column; "  Row: " & target.Row; "  Count: " & target.Cells.Count 'Mike
    ' check to see if target cell is in the input portion of the worksheet
    If (target.Row >= 7) And (target.Row <= 29) Then
        ' check to see if target column is C, D, or G
        If (target.Column = 3) Or (target.Column = 4) Or (target.Column = 7) Then
            Select Case target.Column
                Case 3: intTimeCol = 2  ' set timestamp column to B
                Case 4: intTimeCol = 2  ' set timestamp column to B
                Case 7: intTimeCol = 6  ' set timestamp column to F
            End Select
            
            If target.Text <> "" Then   ' check to see if the target cell is empty
                Cells(target.Row, intTimeCol).Value = Time  ' add timestamp to target row
            Else
                Cells(target.Row, intTimeCol).Value = ""    ' delete timestamp if target cell is empty
            End If
        End If
    End If

End Sub

 

[SBendBuckeye]

Tony, The code above is based on your code. It had been long enough that I didn't remember. Sorry, not trying to take credit for your work.

Have a great day!

j2consulting@yahoo.com

 

[TonyJollans]

No problems

Hardly my code anyway - my contribution was minimal - just the loop

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[