Delay or Pause durring macro after send keys

ASHEXCEL

New Member
Joined
Jan 17, 2019
Messages
6
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

Code:
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

Code:
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

Code:
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

ASHEXCEL

New Member
Joined
Jan 17, 2019
Messages
6
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

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.

:confused::banghead:
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,430
Office Version
  1. 365
Platform
  1. Windows
It always comes back with the sheet name i clicked on not the one i clicked from...

Hi, you could try:

Rich (BB code):
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
MsgBox Sh.Name
End Sub
 

ASHEXCEL

New Member
Joined
Jan 17, 2019
Messages
6
Hi, you could try:

Rich (BB code):
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
MsgBox Sh.Name
End Sub

Erm oh wow (y) 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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,430
Office Version
  1. 365
Platform
  1. Windows
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.

Rich (BB code):
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
MsgBox Sh.Name
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,368
Messages
5,528,272
Members
409,813
Latest member
robyrux

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top