Macro on Window Activation

[Sir Babydum GBE]

Hi

I thought this would be simple and I'm using code that looks like it should work - but it doesn't. I've decided to stop crying and ask for help.

I have an application open on one half of the screen, and Excel on the other half. The excel spreadsheet has a list of reference numbers which I copy and then paste into the other application for researching accounts.

What I want is that when I click the Excel window, it realises it's been activated, moves down one cell and copies the contents. That's it.

I recorded a macro and put it in

Private Sub Worksheet_Activate()
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.Copy

then

Private Sub Workbook_Activate()
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.Copy


then

Private Sub Workbook_SheetActivate(ByVal Sh As Object).
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.Copy


I click away from Excel which activates the other application. I click back on Excel and nothing happens.

Why I ask you, WHY????

Thanks

[rory]

I'm afraid you may have to start crying again...
The Activate events in Excel only occur within Excel itself. So the sheet activate only occurs when you switch from another sheet; similarly the workbook_activate event only occurs when you switch workbooks. Even if you use an application level variable declared WithEvents and trap the Application_WindowActivate event, it will not be triggered by switching from another application. You may have to look at something else altogether, like using APIs to send messages to the other application window with the values you want to use.
Regards,
Rory

[Sir Babydum GBE]

I'm afraid you may have to start crying again...
The Activate events in Excel only occur within Excel itself. So the sheet activate only occurs when you switch from another sheet; similarly the workbook_activate event only occurs when you switch workbooks. Even if you use an application level variable declared WithEvents and trap the Application_WindowActivate event, it will not be triggered by switching from another application. You may have to look at something else altogether, like using APIs to send messages to the other application window with the values you want to use.
Regards,
Rory

Blast Excel!

Thanks Rory

[matthewspatrick]

Sir Babydum,

What impact will this setback have on your intergalactic peace initiatives?

[Ivan F Moala]

You can do this via Application Events class

See chips site for this

http://www.cpearson.com/excel/events.htm

[Sir Babydum GBE]

Sir Babydum,

What impact will this setback have on your intergalactic peace initiatives?

Well, my intergalactic peace initiatives have all gone to pot. I said "Thank you all for coming" at the end of the last peace conference, and this turned out, in their language, to mean "May you all rot in your own entrails".

So I was fired and now work for an energy company - doing nothing (apparently, thanks Bob)

[Sir Babydum GBE]

You can do this via Application Events class

See chips site for this

http://www.cpearson.com/excel/events.htmThanks Ivan,

I'm looking now and trying to use my considerable intellect to work it all out.

[Aussiebear]

How very modest you are Sir BD. You forgot to mention your promotion to the "Mail Room".

[rory]

Sir Babydum,
If you are just switching back and forth between another application and Excel, no application-level events will be triggered unless you do something additional in Excel, such as switching between workbooks.
If you work in the mail room though, you may want to look into the SendMessage or PostMessage APIs... :)
Regards,
Rory
PS I have tested the above in Excel2002 on Windows 2000; I cannot say for certain that a different OS won't have an impact, and I can't test that until later.