Delay or Pause durring macro after send keys

[ASHEXCEL]

Hi all,

Struggling a bit with this one.

Basically i have a sheet which is protected from user changes, easy enough to unprotect to update prior to automatically refreshhing.

My problem is I am using sendkeys to run a refresh on a third party software addin to excel which when runs just on its own code will do exactly what is required and takes a little time.

But after sending the sendkeys i want to pause/delay the macro to enable the third party to do it's things and then run a macro to re protect the sheet.

Here is my code for when i press my command button to run

Sub refresh_PBCS_Data()
Dim ws As Worksheet
Dim Cell_das As Variant
Cell_das = Range("Cell_das")
MsgBox Cell_das
            Sheet5.Unprotect Password:=Cell_das
Call sendkeys_test      'MsgBox ("This application is started!")
Sheet5.Protect Password:=Cell_das, userinterfaceOnly:=True
End Sub

the send keys code is

Sub sendkeys_test()
Application.SendKeys "%"
Application.SendKeys "s"
Application.SendKeys "dr"
Application.SendKeys "r"




End Sub

Now i'd like a pause or delay of a minute after the last send keys line is activated.

But when i try

Application.Wait (Now + TimeValue("0:01:00")

All it seems to do is stop the sendkeys instructions to third party, effectively overriding that command.

As when i run the Sendkeys_test on its own it does what i want it to do i just cannot get a automatic delay afterwards to allow it to complete before processing further.

Any help would be gratefully appreciated all my research so far has drawn a blank and frustration.

Cheers

 

[ASHEXCEL]

I have had a further thought of checking that the sheet is protected or not on sheet deactivate, if so then protect, if not then carry on and move from to the new sheet clicked on by the user.

Seemed a fail safe method when the user clicked another sheet so protection is automatically applied.

However when i tried a test with just this code

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
MsgBox ActiveSheet.Name
End Sub

It always comes back with the sheet name i clicked on not the one i clicked from...

So a little frustrated and confused.

What i just need to do is have a reference to the sheet i have just clicked away from, as the user coudl possible click on any one of 10 sheets, not necessarily in any order either i.e not either side of the original sheet.

 

[FormR]

It always comes back with the sheet name i clicked on not the one i clicked from...

Hi, you could try:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
MsgBox Sh.Name
End Sub

 

[ASHEXCEL]

Hi, you could try:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
MsgBox Sh.Name
End Sub

Erm oh wow many thanks it worked for the msgbox a treat ... I don't understand how that worked instead of my attempt.

But thank you very much i will use this yeah!!!

 

[FormR]

I don't understand how that worked instead of my attempt.

Hi, sh variable is defined within the event and refers to the worksheet that triggered the event rather than the sheet that happens to be currently active.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
MsgBox Sh.Name
End Sub