No, it's not possible to handle "Move or Copy" dialog.
Please, refer these links:
Using Events with Microsoft Excel Objects [Excel 2003 VBA Language Reference][
^]
Application Object Events [Excel 2003 VBA Language Reference][
^]
Using Events with the Application Object (Office 2010)[
^]
You can only handle
new sheet event[
^].
Steps to do:
1) Add new class module (change the Name property to:
MyApp) and copy and paste below code:
Public WithEvents ExcApp As Application
Private Sub ExcApp_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)
MsgBox "New sheet added!"
End Sub
2) Add new module and add insert line to it:
Public oApp As MyApp
3) Go to the ThisWorkbook object (browse it in "Project - VBA Project" window on the left of code pane) and add this code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set oApp = Nothing
End Sub
Private Sub Workbook_Open()
Set oApp = New MyApp
Set oApp.ExcApp = Application
End Sub
4) Set cursor position inside the body of
Workbook_Open()
procedure and push
F5
key
5) Try to add new worksheet or use "Move or Copy" dialog with copy option ;)