[Excel 2013] Issue with modification logbook in case multiple cells content is deleted

[SuperWamJarice]

Good afternoon all,

I am working on a pretty big workbook for which a logbook is automatically filled whenever a cell content is changed in any worksheet. It works quite well but I have an issue when the content of multiple selected cells is deleted. A run-time error '13' type mismatch appears. I understand why it happens, as it cannot enter the content of multiple cell into one cell of the logbook, and I found a way to stop this error from appearing, but what I would like to do is
- either to add a line in the logbook giving less details but showing that something happened in the range,
- or (and that would be the best) add 1 line per cell of the range for which content has been deleted, in the logbook

here is the code I made:

Dim OldVal As Variant

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)


OldVal = Target.Value


End Sub


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    Sheets("LogDetails").UnProtect
   
       If activesheet.Name <> "LogDetails" And activesheet.Name <> "Test" And activesheet.Name <> "Front Page" And activesheet.Name <> "Front Sheet" Then 
           
    Application.EnableEvents = False
    Application.ScreenUpdating = False
           
           If Target.Count > 1 Then 'There is my attempt to log info in case content of multiple cells have been removed
                Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = activesheet.Name
                Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 8).Value = Application.UserName
                Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 9).Value = Date
                Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 10).Value = Time
                Exit Sub
           Else
    
                      
           Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = activesheet.Name


           Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Target.Address(0, 0)
                         
           If OldVal = "" Then
               Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 6).Value = "<vide>"
           Else
               Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 6).Value = OldVal
           End If
                     
           If Target.Value = "" Then
               Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 7).Value = "<vide>"
           Else
               Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 7).Value = Target.Value
           End If
       
           Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 8).Value = Application.UserName


           Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 9).Value = Date
  
           Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 10).Value = Time
           
           Application.EnableEvents = True
           
           End If
        End If
    Sheets("LogDetails").Protect
Application.ScreenUpdating = True
End Sub

I am a beginner with VBA, so my apologize if that code looks ugly.
If I just put :

If Target.Count > 1 Then 
Exit Sub

instead of the whole:

If Target.Count > 1 Then 'There is my attempt to log info in case content of multiple cells have been removed
                Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = activesheet.Name
                Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 8).Value = Application.UserName
                Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 9).Value = Date
                Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 10).Value = Time
                Exit Sub
           Else

my macro doesn't crash but I don't get the record of the changes made.

Could anyone give me a hint to solve that problem?

Thank you very much in advance,

Seb</vide></vide>

 

[SuperWamJarice]

Good afternoon forum people,

New update to my post: End last week, appart from my above mentioned issue, the macro worked just fine. Coming back to work yesterday, it is no longer working. All the other macros in my workbook, actioned through buttons or userforms, are working perfectly. I didn't make any changes to the code, it is not showing any error, it is just doing nothing when I make changes to the worksheets...
Does anyone have any idea of things that could have gone wrong?
If you have ideas regarding the above mentioned issue, it would be welcome as well...

Thank you very much in advance for your help!

Seb

 

[SuperWamJarice]

Hello forum, (I feel like I am having a conversation with myself)

In fact I solved that 2nd issue. I don't know why it stopped working but instead of having :

If tagret.count>1 then
Exit sub
Else
'What its supposed to be doing if target count=1
End if

I put:

If target.count>1 then Exit sub

Still hoping someone can help on my first issue.

Seb

 

[Stiuart_W]

The first thing to try would be to open an Immediate Window in the Developer section and enter
Application.EnableEvents = True

Press return.

If your code stopped working before this was run again events may be disabled on your Excel

 

[SuperWamJarice]

Hi Stiuart_W,

Thank you for your answer. I have to admit that I had never seen that Immediate Window before. I was searching how to use it and it really looks useful. So I typed the Application.EnableEvents = True but nothing happened.

Additional information: If in my code, I replace Application.EnableEvents = False by Application.EnableEvents = True, it returns an error : Run-time error '-2147417848 (80010108)': Method 'Range' of object '_Worksheet' failed
and the debug takes me to :

Sheets("LogDetails").Range("A" & Rows.count).End(xlUp).Offset (1,0).value = activesheet.name

The result of the macro if the Application.EnableEvents = True, is a big mess. If I put set it on False, it does what I want (appart if I try to delete the content of several cells, in which case it just stops the macro)

 

[Stiuart_W]

There's nothing wrong with the code application.enableevents=

When set to False it just means that Workbook or worksheet change events are disabled - so macros won't fire. This stops your worksheet and workbook macros from getting into a recursive loop that it can't get out of because the macro makes a change that fire the macro.

=True just resets so events are re-enabled

As to this line

Sheets("LogDetails").Range("A" & Rows.count).End(xlUp).Offset (1,0).value = activesheet.name

I checked it and it works fine

 

[SuperWamJarice]

Thank you for your feed back. I will keep on trying things to manage and have a record of changes made on several cells simultaneously.