1

I need to automatically press Enter when pop-ups occur in VBA. The problem is that the code generating the pop-ups is a built-in plug-in function which I cannot reach.

How can I manage to press Enter automatically in this case?

I tried Activeworkbook.LockServerFile, but it fails.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Marco Di Bartolo
  • 31
  • 2
  • The question is which popup? What produced the popup? What is the pop-up message? Did you try `Application.DisplayAlerts = False` (set it `True` afterwards)? • If nothing works you can try as last option to use `SendKeys "{ENTER}"` but I don't recommend to do this (as it is not very reliable and can cause strange side effects). And if the popup is loaded modal it doesn't work anyway (but this depends on where the popup comes from). If you provide some more information we can check wich solution will be the best. – Pᴇʜ Feb 03 '20 at 16:12
  • I tried both of them and it does not work. The pop-ups ask for the input of the name of the database from which SQL ( called by VBA) has to query the data to be loaded in a pivottable. I tried to debug with F8, but the pop-ups occur in a string of code not visible in the tool I have, they come from a built in plug in – Marco Di Bartolo Feb 03 '20 at 16:27

1 Answers1

1

You can close the popup by sending it a WM_CLOSE message using SendMessage Win32API. You can retrieve the window handle of the popup, which you need to use SendMessage, by using the below code:

Option Explicit

Private Declare Function FindWindow Lib "User32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetWindowText Lib "User32" Alias "GetWindowTextA" (ByVal hWnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Private Declare Function GetWindowTextLength Lib "User32" Alias "GetWindowTextLengthA" (ByVal hWnd As Long) As Long
Private Declare Function GetWindow Lib "User32" (ByVal hWnd As Long, ByVal wCmd As Long) As Long
Private Declare Function IsWindowVisible Lib "User32" (ByVal hWnd As Long) As Boolean

Private Const GW_HWNDNEXT = 2

Private Sub Test()

    Dim lhWndP As Long
    If GetHandleFromPartialCaption(lhWndP, "Excel") = True Then
        If IsWindowVisible(lhWndP) = True Then
          MsgBox "Found VISIBLE Window Handle: " & lhWndP, vbOKOnly + vbInformation
        Else
          MsgBox "Found INVISIBLE Window Handle: " & lhWndP, vbOKOnly + vbInformation
        End If
    Else
        MsgBox "Window 'Excel' not found!", vbOKOnly + vbExclamation
    End If

End Sub

Private Function GetHandleFromPartialCaption(ByRef lWnd As Long, ByVal sCaption As String) As Boolean

    Dim lhWndP As Long
    Dim sStr As String
    GetHandleFromPartialCaption = False
    lhWndP = FindWindow(vbNullString, vbNullString) 'PARENT WINDOW
    Do While lhWndP <> 0
        sStr = String(GetWindowTextLength(lhWndP) + 1, Chr$(0))
        GetWindowText lhWndP, sStr, Len(sStr)
        sStr = Left$(sStr, Len(sStr) - 1)
        If InStr(1, sStr, sCaption) > 0 Then
            GetHandleFromPartialCaption = True
            lWnd = lhWndP
            Exit Do
        End If
        lhWndP = GetWindow(lhWndP, GW_HWNDNEXT)
    Loop

End Function

From : How to use FindWindow to find a visible or invisible window with a partial name in VBA

Also, see vba close pdf file wtih sendmessage without prompt message for an example of using SendMessage with WM_CLOSE.

Motomotes
  • 4,111
  • 1
  • 25
  • 24
  • If the popup is generated by Excel VBA and because Excel VBA is single threaded the code execution is probably stuck at the popup and the OP will not be able to run any code until the popup is closed. Same if the popup is modal. • So there might be no code solution *after* the popup appeared. – Pᴇʜ Feb 03 '20 at 16:32
  • Excel VBA is single threaded, but I would l think the popup happens on the Excel UI thread and doesn't halt VBA execution. – Motomotes Feb 03 '20 at 16:34
  • But then `SendKeys "{ENTER}"` should work? He sais *"pop-ups is a built-in plug-in"* do plug-ins (probably he meant add-in) run in another thread? I thought thed run in the same thread. – Pᴇʜ Feb 03 '20 at 16:36
  • He may to `DoEvents()` while looking for the window using `GetHandleFromPartialCaption`. Honestly though, he needs to tell us more about what is causing the popup, as there is probably a refactor to his code that could prevent it and prevention and cures are like ounces and pounds, respectively. – Motomotes Feb 03 '20 at 16:36
  • I agree, we need more information about the popup and preventing it would be the best option (if possible). – Pᴇʜ Feb 03 '20 at 16:38
  • 1
    I thought maybe the popup doesn't have focus or the "OK" button doesn't have focus. Maybe he could try `SendKeys` with `O` instead, but really, he needs to avoid the popup. – Motomotes Feb 03 '20 at 16:39