Targeting Sendkeys to a Particular Window using Excel VBA

Hi Everyone,

Sometimes while doing some VBA stuff, we want to target Sendkeys to a particular window which may or may not be active or present. Here is the code which will first check if the Target window is available by trying to activate it by AppActivate and then Sendkeys as desired. If Target window isn't available then you may wait until it shows up or show an error message. So Here are the code:-

1. For waiting until Windows shows up:-

       

Sub Target_Sendkeys()
    On Error Resume Next                    'Resuming next Statement on Error(5)
    Do
        Err.Clear                           'Reseting the Err
        AppActivate "Blogger"               'Put the name of title bar of Target window here.
        DoEvents                            'Avoidings hangs
    Loop While Err.Number = 5
    
    SendKeys "%{ }r", True                  'Restoring the window
    Application.Wait DateAdd("s", 1, Now)   'a halt of one sec
    SendKeys "{ESC})"                       'Cancelling Right click if windows is already restored.
    SendKeys "^t"                           'Sending Ctrl + t shortcut key.
End Sub

       
 

Remarks:-
The above code will wait for the Target window to appear (in my testing Blogger's window in the Chrome Browser) and send the Ctrl (^) + t short cut keys after activating it. You don't need to write the whole name of the Title bar as the AppActivate argument, instead you can use the some Prefix part to refer Target Window.

2. Showing a Message if Target window is not available, otherwise Sendkeys

       

Sub Target_Sendkeys_Msgbox()
    On Error Resume Next                        'Resuming next Statement on Error(5)
    AppActivate "Blogger"
    
    If Err.Number = 5 Then
        MsgBox "Target window is not available."
    Else
        SendKeys "%{ }r", True                  'Restoring the window
        Application.Wait DateAdd("s", 1, Now)   'a halt of one sec
        SendKeys "{ESC})"                       'Cancelling Right click if windows is already restored.
        SendKeys "^t"                           'Sending Ctrl + t shortcut key.
    End If
End Sub


       
 

3. How to restrict a window to show up OR Raining SendKeys (ESC) on a particular window.

Actually, the Case I am going to explain now, was an outcome of the Query which a member asked on Excel forum. Here is the Link of it.
http://www.excelforum.com/excel-programming-vba-macros/1064815-auto-refresh-the-excel-every-1-second-2.html
Actually, the member was thinking of an Auto Updating workbook opened at Main Display. This Workbook was further linked to a Shared Workbook which was updated by many users and the changes were required to be reflected on Main Display in the Real time. I took care of the requirements and was able to update the Main Display as required. But the problem was, Sometimes, the Links to Shared Workbook gets broken and the UPDATE VALUES Dialog box, got shown many times. So to Cancel that Update Values Dialog box, I researched and experimented and got rewarded with what you are reading.

Here is the code for Avoiding particular window:-

       

Sub Restrict_Window()
    Sec = 1
    On Error Resume Next
    AppActivate "Update Values"
    
    If Err.Number = 5 Then
        Application.OnTime DateAdd("s", Sec, Now), "Restrict_Window"
    Else
        SendKeys "{ESC}"
        Application.OnTime DateAdd("s", Sec, Now), "Restrict_Window"
    End If
End Sub

       
 

The above code will catch the Update Values Dialogue Box when pops up and Send an ESC Key to close the same. Actually this is an al1 time running code and needs an extra instance of Excel window to do the job. So better if you install two excel versions at the same time Or Open a separate instance of Excel (using Excel /x in the Run Command).

So it was all from my side. Now its you turn to try it.

Regards,
Vikas Gautam