Automated Date Entry

0

Hello,

I found your automated date entry to be extremely useful for my excel project.

The issue I found is when I delete the row, the date still updates in the cell/column.

I have a student information spreadsheet. If I delete a row, the next row moves up and the date is updated, but that's not what I want to do.  

I want to clear the date if I delete the data in that row. Thank you! 

Code_Goes_Here

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTableRange As Range

Dim myDateTimeRange As Range

Dim myUpdatedRange As Range

'Your data table range

Set myTableRange = Range("AA2:BI500")

'Check if the changed cell is in the data table or not.

If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

'Stop events from running

Application.EnableEvents = False

'Column for the date/time

Set myDateTimeRange = Range("B" & Target.Row)

'Column for last updated date/time

Set myUpdatedRange = Range("C" & Target.Row)

'Determine if the input date/time should change

If myDateTimeRange.Value = "" Then

    myDateTimeRange.Value = Now

End If

'Update the updated date/time value

myUpdatedRange.Value = Now

'Turn events back on

Application.EnableEvents = True

End Sub

Code_Goes_Here

Thank you, 

Erin Mills 

Answer
Discuss

Discussion

It's really, really difficult to tell how to improve your code without seeing the code you have. Can you make the task easier for me? Consider editing your question to include the code imn the post or even attach a copy of your workbook (also possible in Edit mode).
Variatus (rep: 4208) Aug 6, '20 at 8:17 pm
This is the code I have. Once I clear a row, the date cells are still updated. I want to leave them blank if the table range is cleared. Thank you!


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myTableRange As Range Dim myDateTimeRange As Range Dim myUpdatedRange As Range   'Your data table range Set myTableRange = Range("AA2:BI500")   'Check if the changed cell is in the data table or not. If Intersect(Target, myTableRange) Is Nothing Then Exit Sub   'Stop events from running Application.EnableEvents = False   'Column for the date/time Set myDateTimeRange = Range("B" & Target.Row) 'Column for last updated date/time Set myUpdatedRange = Range("C" & Target.Row)   'Determine if the input date/time should change If myDateTimeRange.Value = "" Then       myDateTimeRange.Value = Now   End If   'Update the updated date/time value myUpdatedRange.Value = Now   'Turn events back on Application.EnableEvents = True End Sub
emills Aug 7, '20 at 8:24 am
Please go back to your question and press Edit. Then add the code to your question, making sure to enclose it within Code tags. In that way it can be read and copied.
Variatus (rep: 4208) Aug 8, '20 at 4:11 am
You can either first insert a pair of tags, select the passage "Code_goes_here" and paste your code in its place, or you can paste your code first, select the code you pasted and then press the code tag button to let the site's software surround the code you selected with tags.
Variatus (rep: 4208) Aug 8, '20 at 9:21 pm
Add to Discussion

Answers

0

Hahaha. It seems that Don's solution isn't quite as idiot-proof as he thought. The fact that I came across this for the first time today makes me wonder if the problem existed prior to Office 365. But it does now, affects not only deletions of rows but also columns and the additions of both, and there is no cure you would consider simple. Please replace all of your existing code with all of the below.

Option Explicit

Private Ccount As Long              ' count of currently used columns
Private Rcount As Long              ' count of currently used rows

Private Enum Nct                    ' named index numbers for GetCount
    ' 075
    NctClm = 1
    NctRow
End Enum

Private Sub Worksheet_Activate()
    ' 075
    ' record current count when activating the tab
    SetCount
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 075

    Dim myTableRange As Range
    Dim myDateTimeRange As Range
    Dim myUpdatedRange As Range

    'Your data table range
    Set myTableRange = Range("AA2:BI500")

    ' check if the change resulted from rows or columns being added or deleted
    If (GetCount(NctClm) <> Ccount) Or (GetCount(NctRow) <> Rcount) Then
        SetCount
        Exit Sub
    End If

    'Check if the changed cell is in the data table or not.
    If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

    'Stop events from running
    Application.EnableEvents = False
    'Column for the date/time
    Set myDateTimeRange = Range("B" & Target.Row)
    'Column for last updated date/time
    Set myUpdatedRange = Range("C" & Target.Row)

    'Determine if the input date/time should change
    If myDateTimeRange.Value = "" Then
        myDateTimeRange.Value = Now
    End If

    'Update the updated date/time value
    myUpdatedRange.Value = Now

    'Turn events back on
    Application.EnableEvents = True
End Sub

Private Sub SetCount()
    ' 075
    ' record the current count
    With ActiveSheet.UsedRange
        Ccount = GetCount(NctClm)
        Rcount = GetCount(NctRow)
    End With
End Sub

Private Function GetCount(ByVal Ix As Nct) As Long
    ' 075
    ' return the count specified by Ix
    With ActiveSheet.UsedRange
        If Ix = NctClm Then
            GetCount = .Columns.Count
        Else
            GetCount = .Rows.Count
        End If
    End With
End Function

The idea implemented here starts from the fact that every worksheet has a UsedRange, which, simply put, is the range currently in use. When rows or columns are added or deleted the size of the UsedRange changes. This change can be captured by comparing the size of the range before and after while the change itself is captured by the Worksheet_Change event.

So, the above code records the size of the UsedRange when the worksheet is first activated. On every occurrence of the Worksheet_Change event the UsedRange's current size is compared to what it was before. If there is a change the record is updated and the date insertion suppressed. If there was no change in the size of the UsedRange everything goes on as before.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login