When Target.Value is Deleted/Cleared

V

vzq032372

New Member
Joined
Dec 17, 2015
Messages
42
I have the following the Worksheet's module and it all works fine (i.e. when the value is changed, when a value is added where the cell was previously empty/null). However, the issue is when the value in the cell is deleted.
If there is a value in the field and it's deleted, the vOldValue holds a value but then I get an error if the vNewValue is now empty. I've highlighted where I'm stuck, I tried using IsEmpty, IsNull, = "" and no version seems to work. Hoping someone can help, thanks in advance.

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Range("B39", "B42"), Target) Is Nothing Then
    Else
    Exit Sub
    End If
End Sub

Code: 
Sub Worksheet_Change(ByVal Target As Range)
Application.DisplayAlerts = False
Application.ScreenUpdating = False
    
    Static blnAlreadyBeenHere As Boolean
    'This piece avoid to execute Worksheet_Change again
    If blnAlreadyBeenHere Then
        blnAlreadyBeenHere = False
        Exit Sub
    End If

    'Now, we will store the old and new value
    Dim vOldValue As Variant
    Dim vNewValue As Variant

    'To store new value <===========================================================THIS IS WHERE THE ISSUE LIES
    If IsEmpty(Target.Value) = True Then
    vNewValue = "Deleted"
    Else
    vNewValue = Target.Value
    End If
Debug.Print vNewValue


    'Undo to retrieve old value
    'To avoid new Worksheet_Change execution
    blnAlreadyBeenHere = True
    Application.Undo

    'To store old value
    vOldValue = Target.Value
Debug.Print vOldValue
    'To rewrite new value

    'To avoid new Worksheet_Change execution agein
    blnAlreadyBeenHere = True
    Target.Value = vNewValue

    Debug.Print vOldValue, vNewValue
    
    Dim X As Integer
        Set wb = ThisWorkbook
        ShtName = "Revision History"
    If Target.Cells.Count > 1 Then Exit Sub
    X = EndRow + 1
'    OldVal = Target.Value
    wb.Sheets(ShtName).Range("AA" & X).Formula = "=Now()"  'Revision Date
    wb.Sheets(ShtName).Range("AB" & X).Value = ActiveSheet.Name 'Sheet that was changed
    wb.Sheets(ShtName).Range("AC" & X).Value = Target.Address ' Cell Changed
    wb.Sheets(ShtName).Range("AD" & X).Value = vOldValue ' Changed from
    wb.Sheets(ShtName).Range("AE" & X).Value = vNewValue ' Changed To
    wb.Sheets(ShtName).Range("AF" & X).Value = Environ("username") ' User that made change

    With wb.Sheets(ShtName)
    .Range("AA" & X).Copy
    .Range("AA" & X).PasteSpecial Paste:=xlPasteValues, _
     Operation:=xlNone, SkipBlanks _
           :=False, Transpose:=False
       Application.CutCopyMode = False
    End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Saying you get an error without providing the number and the error description doesn't help a lot. I tried your code and it didn't raise any error, but I suspect there's more than what you posted. When I deleted the value, the debug output was "Deleted".

The only potential issues I see is that your boolean will always be False, as that is the default value. Each time the sub starts, its value is False. Then there is turning off application settings without having an error handler (I presume you reset them but don't show it). If your sub errors out, those changes are held.
 
Upvote 0
The code didn't error out for me either.
It is continually overwriting row 1 of the Revision History sheet though, is that really what you want ?
Also you seem to be using a lot of code involving blnAlreadyBeenHere to do what Application.EnableEvents = False / True does in a much more efficient manner.

Give this a try:
Rich (BB code): 
Sub Worksheet_Change(ByVal Target As Range)

    Dim wb As Workbook
    Dim ShtName As String
    Dim EndRow As Long

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    'Now, we will store the old and new value
    Dim vOldValue As Variant
    Dim vNewValue As Variant

    'To store new value <===========================================================THIS IS WHERE THE ISSUE LIES
    If IsEmpty(Target.Value) = True Then
        vNewValue = "Deleted"
    Else
        vNewValue = Target.Value
    End If
Debug.Print vNewValue


    'Undo to retrieve old value
    'To avoid new Worksheet_Change execution
    Application.EnableEvents = False
    Application.Undo

    'To store old value
    vOldValue = Target.Value
Debug.Print vOldValue
    'To rewrite new value
    Target.Value = vNewValue

Debug.Print vOldValue, vNewValue
    Application.EnableEvents = True

    Dim X As Integer
    Set wb = ThisWorkbook
    ShtName = "Revision History"
    If Target.Cells.Count > 1 Then Exit Sub

    With wb.Sheets(ShtName)
        EndRow = .Cells(.Rows.Count, "AA").End(xlUp).Row
        If .Range("AA" & EndRow) = "" Then
            X = EndRow
        Else
            X = EndRow + 1
        End If
        .Range("AA" & X).Value = Now  'Revision Date
        .Range("AB" & X).Value = ActiveSheet.Name 'Sheet that was changed
        .Range("AC" & X).Value = Target.Address ' Cell Changed
        .Range("AD" & X).Value = vOldValue ' Changed from
        .Range("AE" & X).Value = vNewValue ' Changed To
        .Range("AF" & X).Value = Environ("username") ' User that made change
    End With

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
Micron said:
Saying you get an error without providing the number and the error description doesn't help a lot. I tried your code and it didn't raise any error, but I suspect there's more than what you posted. When I deleted the value, the debug output was "Deleted".

The only potential issues I see is that your boolean will always be False, as that is the default value. Each time the sub starts, its value is False. Then there is turning off application settings without having an error handler (I presume you reset them but don't show it). If your sub errors out, those changes are held.
Click to expand...
The error is Run-time error '13': Type mismatch.
What I did is that I just deleted the value in the cell and that's where the error is produced because it's not holding a value in vNewValue.
 
Upvote 0
Alex Blakenburg said:
The code didn't error out for me either.
It is continually overwriting row 1 of the Revision History sheet though, is that really what you want ?
Also you seem to be using a lot of code involving blnAlreadyBeenHere to do what Application.EnableEvents = False / True does in a much more efficient manner.

Give this a try:
Rich (BB code): 
Sub Worksheet_Change(ByVal Target As Range)

    Dim wb As Workbook
    Dim ShtName As String
    Dim EndRow As Long

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    'Now, we will store the old and new value
    Dim vOldValue As Variant
    Dim vNewValue As Variant

    'To store new value <===========================================================THIS IS WHERE THE ISSUE LIES
    If IsEmpty(Target.Value) = True Then
        vNewValue = "Deleted"
    Else
        vNewValue = Target.Value
    End If
Debug.Print vNewValue


    'Undo to retrieve old value
    'To avoid new Worksheet_Change execution
    Application.EnableEvents = False
    Application.Undo

    'To store old value
    vOldValue = Target.Value
Debug.Print vOldValue
    'To rewrite new value
    Target.Value = vNewValue

Debug.Print vOldValue, vNewValue
    Application.EnableEvents = True

    Dim X As Integer
    Set wb = ThisWorkbook
    ShtName = "Revision History"
    If Target.Cells.Count > 1 Then Exit Sub

    With wb.Sheets(ShtName)
        EndRow = .Cells(.Rows.Count, "AA").End(xlUp).Row
        If .Range("AA" & EndRow) = "" Then
            X = EndRow
        Else
            X = EndRow + 1
        End If
        .Range("AA" & X).Value = Now  'Revision Date
        .Range("AB" & X).Value = ActiveSheet.Name 'Sheet that was changed
        .Range("AC" & X).Value = Target.Address ' Cell Changed
        .Range("AD" & X).Value = vOldValue ' Changed from
        .Range("AE" & X).Value = vNewValue ' Changed To
        .Range("AF" & X).Value = Environ("username") ' User that made change
    End With

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
Click to expand...
It was a function for EndRow that I didn't include, thank you for your suggestion. I copied the entire code as you wrote it and did the following:
Clicked on B42 which was empty
Typed something in
The code worked and registered the changes with the vOldValue showing as empty in the table.

I then went back to B42
Deleted the contents in the cell by hitting the delete key only and pressed enter
and that's where the error pops up: Run-time error '13': Type Mismatch stopping at Debug.Print vNewValue

After reading the content in microsoft.com, I changed
VBA Code: 
    Dim vOldValue As Variant
    Dim vNewValue As Variant
to
VBA Code: 
    Dim vOldValue As String
    Dim vNewValue As String
and changed
Code: 
    If IsEmpty(Target.Value) = True Then
        vNewValue = "Deleted"
    Else
        vNewValue = Target.Value
    End If
to
Code: 
    If Target.Value = "" Or IsNull(Target.Value) Or IsEmpty(Target.Value) Then
        vNewValue = "Deleted"
    Else
        vNewValue = Target.Value
    End If

and it produced the exact same error.
 
Upvote 0
Alex Blakenburg said:
The code didn't error out for me either.
It is continually overwriting row 1 of the Revision History sheet though, is that really what you want ?
Also you seem to be using a lot of code involving blnAlreadyBeenHere to do what Application.EnableEvents = False / True does in a much more efficient manner.

Give this a try:
Rich (BB code): 
Sub Worksheet_Change(ByVal Target As Range)

    Dim wb As Workbook
    Dim ShtName As String
    Dim EndRow As Long

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    'Now, we will store the old and new value
    Dim vOldValue As Variant
    Dim vNewValue As Variant

    'To store new value <===========================================================THIS IS WHERE THE ISSUE LIES
    If IsEmpty(Target.Value) = True Then
        vNewValue = "Deleted"
    Else
        vNewValue = Target.Value
    End If
Debug.Print vNewValue


    'Undo to retrieve old value
    'To avoid new Worksheet_Change execution
    Application.EnableEvents = False
    Application.Undo

    'To store old value
    vOldValue = Target.Value
Debug.Print vOldValue
    'To rewrite new value
    Target.Value = vNewValue

Debug.Print vOldValue, vNewValue
    Application.EnableEvents = True

    Dim X As Integer
    Set wb = ThisWorkbook
    ShtName = "Revision History"
    If Target.Cells.Count > 1 Then Exit Sub

    With wb.Sheets(ShtName)
        EndRow = .Cells(.Rows.Count, "AA").End(xlUp).Row
        If .Range("AA" & EndRow) = "" Then
            X = EndRow
        Else
            X = EndRow + 1
        End If
        .Range("AA" & X).Value = Now  'Revision Date
        .Range("AB" & X).Value = ActiveSheet.Name 'Sheet that was changed
        .Range("AC" & X).Value = Target.Address ' Cell Changed
        .Range("AD" & X).Value = vOldValue ' Changed from
        .Range("AE" & X).Value = vNewValue ' Changed To
        .Range("AF" & X).Value = Environ("username") ' User that made change
    End With

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
Click to expand...
It seems the issue was on that particular sheet because I was able to run it without issue on another tab. Thanks for the help!
 
Upvote 0
Is there a formula resulting in an error in a cell (e.g. N/A) that your code is referencing? Or by that last post are you saying you solved it?
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Simplify my working code please
Replies
7
Views
224
ipbr21054
I
I
  • Question Question
Value not shown in ComboBox1 when code run
Replies
1
Views
74
ipbr21054
I
D
  • Question Question
Make the code work in a protected sheet
Replies
7
Views
328
dmt32
D
S
  • Solved
Help with Code for Ambiguous Error
Replies
3
Views
98
Fuji
F
B
  • Solved
VBA is there a way to speed up?
Replies
4
Views
421
RoryA
RoryA

Forum statistics

Threads
1,225,322
Messages
6,184,277
Members
453,225
Latest member
adelphiaUK

Share this page

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
Back
Top