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

SuperWamJarice

New Member
Joined
Jan 10, 2018
Messages
9
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:
Code:
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 :
Code:
If Target.Count > 1 Then 
Exit Sub
instead of the whole:
Code:
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

New Member
Joined
Jan 10, 2018
Messages
9
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

New Member
Joined
Jan 10, 2018
Messages
9
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 :
Code:
If tagret.count>1 then
Exit sub
Else
'What its supposed to be doing if target count=1
End if
I put:
Code:
If target.count>1 then Exit sub
Still hoping someone can help on my first issue.

Seb
 

Stiuart_W

Well-known Member
Joined
Jul 3, 2013
Messages
516
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

New Member
Joined
Jan 10, 2018
Messages
9
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 :
Code:
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

Well-known Member
Joined
Jul 3, 2013
Messages
516
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

New Member
Joined
Jan 10, 2018
Messages
9
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.
 

Forum statistics

Threads
1,081,415
Messages
5,358,533
Members
400,502
Latest member
price83

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top