VBA: Copy Event?

[Ganjin]

Hiya, I have used event macros before, but I am not quite sure how to implement this.
And, truth be told, though it would be convenient to have, it is also something I can deal without.

But, here is the situation:
I have a long list of parts numbers in Column B.
Normally, I would select the first part, copy it, paste it into the company program (enter the other details), then select the second part, copy it, paste it into the company program (enter the other details), etc...

I would like to eliminate the need to select the next and following parts.
So, for example, I would select the first part, copy it, paste it into the company program (enter the other details), then copy the second part, paste it into the system (enter the other details), then copy the third part, paste it into the system (enter the other details), etc...

Thus, after the first part, I could just use Ctrl+C for the rest of the list.

I hope this makes sense and that someone has an idea on how to implement this.

 

[AD_Taylor]

I don't know of any copy event specifically but assuming that you are moving between windows, i.e. from the Excel window to the comapny program window, then the Excel workbook should become deactivated.

Try this code in the ThisWorkbook module:

Private Sub Workbook_Deactivate()    
    ActiveCell.Offset(1, 0).Select
End Sub

It basically just selects the next cell down after you move out of the Excel window. Therefore when you come back a simple Ctrl + C should copy the next value?

HTH!

PS Depending on how you paste it into the company program (just pasting or clicking a field and then pasting as examples) it may even be possible to automate the entire copy & paste procedure...

 

[Ganjin]

Thank you for helping me out, Taylor ^^

Hmm, interesting =)
But, though automating the whole process would be awesome.
It is probably well beyond my abilities to do so

Anyhow, this workbook deactivate event is a solid idea, but I have encountered a strange problem.
For some reason it only triggers when the workbook is being opened or just before it is closed.
Switching between the applications through the taskbar or Alt+Tab has no effect.

Is there some setting that needs to be set or changed first?

 

[RoryA]

The workbook_deactivate event only works when switching between windows within excel - changing to another windows program will not trigger it (or any other excel event)

 

[AD_Taylor]

If you let me know the exact step by step procedure you go through when copy and pasting the data (and by this I mean every mouse click or button press) then I should be able to tell you if I think automating it is viable.
Also would need the name of the company program. For example if I were copy and pasting to Chrome I would need to use "Google Chrome".

@Rory - Thanks for the info. I didn't know that and thought it activated at different times.

 

[Ganjin]

Ah, in that case, is there an alternative method?
I first start by adjusting the window size for both programs (excel and the adp kcml client the company uses) so that each occupies about half the screen.

I start with selecting and copying the first part in excel (on the left side of the screen) and pasting it into the appropriate section for the company program (on the right side of the screen).
I then switch back, after filing out the other details, to excel (using the mouse) and select+copy the next part and repeat for all other parts involved.

I guess I could just use a simple macro bound to Ctrl+C or something, but if it can be done (somehow) through an event macro that would be great (it would also give me something new to play around with)

 

[Ganjin]

So, anyone have an idea how to get this event to trigger by just switching windows via the taskbar >.<?Or be this truly a forsakened attempt T-T?