BeforeRemoteChange

[Erdrick]

Hello,

I am having issues getting the BeforeRemoteChange event to trigger properly. I can not get either the before or after events to trigger. I have the following code that i know partially works, because it is triggered for sheet changes events. This tells me my app is setup properly, but i get nothing when others make changes to the worksheet remotely. Any ideas?

Option Explicit
Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

Private Sub App_WorkbookBeforeRemoteChange(ByVal Wb As Workbook)
 MsgBox Wb.Name & " is being remotely changed"
End Sub

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Debug.Print "local change detected"
End Sub

Private Sub App_WorkbookAfterRemoteChange(ByVal Wb As Workbook)
    Debug.Print "After change detected"
End Sub

 

[bobsan42]

Afaik you can only do this (withevents) in a class module.
Is this a shared workbook?

 

[Erdrick]

Afaik you can only do this (withevents) in a class module.

I had tried it that way as well, and didn't work. We will code that way, and post results.

 

[bobsan42]

Consider wether you need the application object, or maybe it makes more sense to use the workbook object.

 

[Erdrick]

Huh, you are right. Not sure what I did wrong the first time. It is working like a charm now. Thanks for the push back in the right direction.

ThisWorkbook

Option Explicit

Dim Obj As New AppClass

Private Sub Workbook_Open()
 Set Obj.App = Application
End Sub

Class: App Class

Option Explicit

Public WithEvents App As Application

Private Sub App_WorkbookBeforeRemoteChange(ByVal Wb As Workbook)
    Debug.Print "Before change detected"
End Sub

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Debug.Print "local change detected"
End Sub

Private Sub App_WorkbookAfterRemoteChange(ByVal Wb As Workbook)
    Debug.Print "After change detected"
End Sub

 

[bobsan42]

Huh, you are right. Not sure what I did wrong the first time. It is working like a charm now. Thanks for the push back in the right direction.

ThisWorkbook

Option Explicit

Dim Obj As New AppClass

Private Sub Workbook_Open()
 Set Obj.App = Application
End Sub

Class: App Class

Option Explicit

Public WithEvents App As Application

Private Sub App_WorkbookBeforeRemoteChange(ByVal Wb As Workbook)
    Debug.Print "Before change detected"
End Sub

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Debug.Print "local change detected"
End Sub

Private Sub App_WorkbookAfterRemoteChange(ByVal Wb As Workbook)
    Debug.Print "After change detected"
End Sub

Glad I could help ?

 

[Erdrick]

It is my understanding that BeforeRemoteChange must be accessed from the application level.

 

[bobsan42]

If it works then I guess you are totally right. I can experiment a bit tomorrow and post the results. But as a general rule i decided for myself i will stay away from shared workbooks to the extent possible.