Excel 2019

Application WorkbookAfterRemoteChange, WorkbookBeforeRemoteChange & WorkbookSync Events

In this tutorial, you’ll learn how to capture WorkbookAfterRemoteChange, WorkbookBeforeRemoteChange and WorkbookSync events. These are the application-level events and triggered when any workbook gets changed remotely. Let’s open VBE (press Alt+F11) and write the following code in ThisWorkbook code module. This code enables your workbook to listen to application object events (for details, visit how to capture application events):

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

WorkbookAfterRemoteChange

Syntax: App_WorkbookAfterRemoteChange(Wb)

The WorkbookAfterRemoteChange event occurs after changes by a remote user are merged into any workbook. The App_WorkbookAfterRemoteChange has one argument Wb which represents the remotely changed workbook.

App_WorkbookAfterRemoteChange Example:

Option Explicit
Public WithEvents App As Application

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

Private Sub Workbook_Open()
 Set App = Application
End Sub

The WorkbookAfterRemoteChange is an application-level event that affects all open workbooks in an Excel session. To work with a particular workbook use the Workbook_AfterRemoteChange procedure.

WorkbookBeforeRemoteChange

Syntax: App_WorkbookBeforeRemoteChange(Wb)

The WorkbookBeforeRemoteChange event occurs before changes by a remote user are merged into a workbook. The App_WorkbookBeforeRemoteChange procedure has one argument Wb which represents the workbook is being changed.

App_WorkbookBeforeRemoteChange Example:

Option Explicit
Public WithEvents App As Application

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

Private Sub Workbook_Open()
 Set App = Application
End Sub

The WorkbookBeforeRemoteChange is an application-level event that affects all open workbooks in an Excel session. To work with a particular workbook use the Workbook_BeforeRemoteChange procedure.

WorkbookSync

Syntax: App_WorkbookSync(Wb, SyncEventType)

[Deprecated] This WorkbookSync event occurs when the user synchronizes the local copy of a sheet in a workbook that is part of a document workspace with the copy on the server. This event as two arguments:

  1. Wb
    The synchronized workbook
  2. SyncEventType
    The status of the synchronization.

Note: This event has been deprecated; it’s used only for backward compatibility.

App_WorkbookSync Example:

Option Explicit
Public WithEvents App As Application

Private Sub App_WorkbookSync(ByVal Wb As Workbook, ByVal SyncEventType As Office.MsoSyncEventType)
 Select Case SyncEventType
  Case msoSyncEventDownloadFailed
   MsgBox Wb.Name & " Sync Download Failed"
  Case msoSyncEventDownloadInitiated
   MsgBox Wb.Name & " Sync Download Initiated"
  Case msoSyncEventDownloadNoChange
   MsgBox Wb.Name & " Sync Download No Change"
  Case msoSyncEventDownloadSucceeded
   MsgBox Wb.Name & " Sync Download Succeeded"
  Case msoSyncEventOffline
   MsgBox Wb.Name & " Sync Offline"
  Case msoSyncEventUploadFailed
   MsgBox Wb.Name & " Sync Upload Failed"
  Case msoSyncEventUploadInitiated
   MsgBox Wb.Name & " Sync Upload Initiated"
  Case msoSyncEventUploadSucceeded
   MsgBox Wb.Name & " Sync Upload Succeeded"
 End Select
End Sub

Private Sub Workbook_Open()
 Set App = Application
End Sub
Advertisement:
Advertisement: