CODEWITHSUNDEEP
vbaexcel
Nicholas
Nicholas

Reputation: 2668

Referencing the selection in deactivated Workbook

Private Sub Workbook_Deactivate()
    ThisWorkbook.ActiveSheet.Selection.Copy
End Sub

I'd like to copy the selection in the deactivated workbook. But this doesn't work, indeed, I got a run-time error.

Upvotes: 1

Views: 109

Answers (1)

vacip
vacip

Reputation: 5426

Damn! It is possible!

Private Sub Workbook_Deactivate()

  ThisWorkbook.Windows(1).Selection.Copy

End Sub

Explanation:

Both ActiveSheet and Selection are children objects of the Application object, not the Workshet or Workbook objects. (See MSDN: Selection and ActiveSheet.) Selection means the currently selected item in the active window. Same for ActiveSheet.

Now it turns out that you can specify a window object for both of them. Normally every Workbook has one window, but you can create multiple windows for each open workbook. Anyhow, every open workbook that you can click and select will have at least one window. This is whateverworkbook.Windows(1).

ThisWorkbook always references the workbook running the macro (that means the workbook actually containing the VBA code). In your case, that is the one you are leaving. (Unless you want the macro to run from any workbook you are deactivating, in which case you'd have to come up with a custom event handler that catches each workbook deactivate event...)

And that's it, really. You can reference a selection in another workbook, you just have to attack it through it's Window.

Thanks for the good question, I have learned something very valuable today. :)

Upvotes: 1

Related Questions