VBA AutoDate: Clearing Autodate after information is deleted from cells

SBC

New Member
Joined
Sep 18, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
My spreadsheet layout is: A col is where labels are scanned in, B col is where shipping labels are scanned in, C Col is where the autodate feeds into.

Individual labels are scanned in until the container is full then click over or arrow over to B col to scan in the shipping label at which point the autodate is filled in col C via VBA.

My issue is that if someone forgets to switch the active cell back over they start scanning in labels again and don't pay attention they end up with 4 or 5 labels in the wrong column so what they do is delete them then rescan them. The autodate will not delete for all the entries and i get a Run-time error '1004': Application defined or object defined error

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date
ActiveSheet.Unprotect
Dim Cell As Range
For Each Cell In Target
    If Cell.Column = Range("B:B").Column Then
        If Cell.Value <> "" Then
        Cells(Cell.Row, "C").Value = Now
        
        Else
            Cells(Cell.Row, "C").Value = ""
        End If
    End If
If Cells(Cell.Row, "C").Value <> "" Then
ActiveWorkbook.Save


End If

Next Cell

ActiveSheet.Protect
End Sub

As you can see i have a couple other things going on as well, unprotecting the sheet so the autodate can fill in a protected cell, as well as saving once a cell in col C has been filled, then reprotecting the sheet after all everything is completed.

As i think about it as i type could this protecting/unprotecting be my issue?

I have no issue with the autodate feeding in nor do i have an issue with deleting 1 cell in col B, its only when i delete multiples in col B that it wont clear all the date stamps in C and throws the error up

Basic knowledge here so please explain your answer throughly
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
I am not sure how you are scanning the data into column B but give this a try and see if it fixes the problem. it replaces current code.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date
ActiveSheet.Unprotect
    If Not Intersect(Target, Columns("B")) Is Nothing Then
        Target.Offset(, 1).Value = Now
    Else
        Target.Offset(, 1).Value = ""
    End If
ActiveWorkbook.Save
ActiveSheet.Protect
End Sub
 

SBC

New Member
Joined
Sep 18, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Sorry its taken so long to reply, been traveling and have not had the time to spend on this side project. This is the error its throwing up at me when i run this

1601334750545.png


If i run debug it shows Target.Offset(, 1).Value = "" in yellow then crashes out
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
The message says it does not like the value property in the statement so let's try without "value". Let Excel decide what it is.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date
ActiveSheet.Unprotect
    If Not Intersect(Target, Columns("B")) Is Nothing Then
        Target.Offset(, 1) = Now
    Else
        Target.Offset(, 1) = ""
    End If
ActiveWorkbook.Save
ActiveSheet.Protect
End Sub
 

SBC

New Member
Joined
Sep 18, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
same outcome only it says "_Default" of object 'Range' failed

When i enter a number into column B and hit enter it pops up the blue circle of thinking for a good 3-5 seconds before it pops up the error
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
One thing it needs is to disable the event trigger while it is posting in column C. See if you still get the message with this modified version
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date
ActiveSheet.Unprotect
[COLOR=rgb(85, 57, 130)]Application.EnableEvents = False[/COLOR]
    If Not Intersect(Target, Columns("B")) Is Nothing Then
        Target.Offset(, 1) = Now
    Else
        Target.Offset(, 1) = ""
    End If
ActiveWorkbook.Save
[COLOR=rgb(85, 57, 130)]Application.EnableEvents = True[/COLOR]
ActiveSheet.Protect
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,353
Messages
5,601,136
Members
414,430
Latest member
jtdinh205

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top