Solved: log only once per 5 sec. - only fix code

[danovkos]

Hi all,
i have this code for writing log about my wb. When user do some action it write log. Problem is, that some action in my sheet runs other action and then the Log file contain duplicity or triplicity in the same time or in 2-3 sec. I know that it does it right, but i need only one log per e.g. 5 sec.
How fix it? But i dont want to slwing down work with this wb with waiting or something similiar.
thx.


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
LogInformation Format(Now, "dd" & "." & "mm" & "." & "yy" & " " & "hh:mm:ss") & ";" _
& vbTab & Application.UserName & ";" & vbTab & vbTab & "Changed" & ";" & vbTab & _
Selection.Address & ";" & vbTab & vbTab & ActiveSheet.Name

End Sub

[Bob Phillips]

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

On Error Goto ws_exit
Application.EnableEvents = False

LogInformation Format(Now, "dd" & "." & "mm" & "." & "yy" & " " & "hh:mm:ss") & ";" _
& vbTab & Application.UserName & ";" & vbTab & vbTab & "Changed" & ";" & vbTab & _
Selection.Address & ";" & vbTab & vbTab & ActiveSheet.Name

ws_exit:
Application.EnableEvents = True
End Sub

[danovkos]

thx for help,
but maybe i was not clear. The other actions are not other macros. It is e.g. user paste value, and if he does this, it makes copy in other sheet and paste in other cell too. So it did 2 actions 2 diferent sheets, and then there aree 2 logs. I meaned solutions like, do existing code (write one log), but wait 5 sec. to writting again if is action. I dont know if i am cleare. :(

[Bob Phillips]

All I did was to try and reduce the duplicates by removing the cascade of changes.

[danovkos]

but this fix from you, didnt stops duplicate log write or?

i tried it now, and it wrote duplicates again. It is because if i good understand your fix, it only stops starting others macros or?
But my duplicates write only the one code. The problem is, that there is one action from user but sometimes (in some sheets) it do 2 or 3 actions in wb. And all this actions wrote as log. But i want only the one from user.

[p45cal]

see if this goes some way towards a solution:Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Static TimeBlock
If TimeBlock >= Now Then Exit Sub
TimeBlock = Now + TimeValue("00:00:05")
On Error GoTo ws_exit
Application.EnableEvents = False

LogInformation Format(Now, "dd" & "." & "mm" & "." & "yy" & " " & "hh:mm:ss") & ";" _
& vbTab & Application.UserName & ";" & vbTab & vbTab & "Changed" & ";" & vbTab & _
Selection.Address & ";" & vbTab & vbTab & ActiveSheet.Name

ws_exit:
Application.EnableEvents = True
End Sub
I've left xld's enable events statements in but I'm not sure whether you want them since you may want some other events to trigger.

[danovkos]

p45cal this looks good,
i did quick try and it works exactly as i wished
i will test it more
but thank you very much m8s :)