Get hwnd of Excel InputBox Method

Question

I think I have a fairly simple question. I'm looking for a method to get the hwnd for an excel input box. I'm automating a process and I'm noticing that a type 8 input box is consistently underneath the excel window (I'm automating excel from another application if that is helpful.). Obviously, I'd like it to show up on top, and I'm attempting to use the SetForegroundWindow function. Any advice?

By request, the only thing I've found that seemed worth trying:

Public Function GetHwnd() as Long
     GetHwnd = Excel.Application.InputBox.hwnd
End Function

Omnidirectional

If you have code which doesn't work, then it's better to include it. – Moderate

I'll add it, but it's more or less useless. I just need some way to get the hwnd of that input box. – Omnidirectional

Does this answer help? https://mcmap.net/q/985230/-passing-value-to-excel-inputbox-from-vb-net/2258 – Annihilator

@RichardMorgan it should answer the question :) OP will need to use the FindWindow and FindWindowEx and SendMessage functions from WinAPI. These are included in the accepted answer at the linked-to question. – Berkie

@RichardMorgan Technicaly yes, technically no. It got me on the right track to what I thought I was looking for, but it turns out I simply needed to adjust the timing and order of how I was hiding and displaying windows to get the effect I was looking for. As far as finding the input box window, since the code haults on that line, I'm not sure you could wirte code in vba to find the handle of that window. If that makes sense, but I might not be right about it either. In any event, I found a way to resolve my issue, and thank you all for the help and suggestions! – Omnidirectional

Any answer needs to address the concept of a 'modal' dialog: the InputBox grabs the focus and the flow of logic - your VBA has handed control to the modal object, and won't run any commands until the modal object hands back control. – Bookseller

Answer 1

It's not so simple a question - and the answer works around several frustrating gaps in VBA.

The VBA.InputBox function creates a 'Modal Dialog' which leaves your application's VBA code in a waiting state at the exact moment you needed VBA to grab the Window Handle and call some or other API function.

By the time the 'modal' state is released, allowing VBA to run commands and API functions again, the InputBox has gone away.

Fortunately, a workaround was discovered in October 2003 by 'manish1239', who posted an ingenious hack on Xtreme Visual Basic Talk: he put the code you need to run in a VBA function that runs around that waiting state, using a delayed callback from an API Timer.

I used his code to set 'PasswordChars' in a VBA InputBox: it's an API call that needs the InputBox window handle, and you can adapt the code for your needs

Public Function InputBoxPassword(Prompt As String, _
                                 Optional Default As String = vbNullString, _
                                 Optional XPos, Optional YPos, _
                                 Optional HelpFile, Optional HelpContext _
                                 ) As String
On Error Resume Next

' Replicates the functionality of a VBA InputBox function, with the user's
' typed input displayed as asterisks. The 'Title' parameter for the dialog
' caption is hardcoded as "Password Required" in this implementation.

' REQUIRED function: TimerProcInputBox
' REQUIRED API declarations: FindWindow, FindWindowEx, SetTimer, KillTimer

' Nigel Heffernan, January 2015, 

' **** **** **** *** THIS CODE IS IN THE PUBLIC DOMAIN **** **** **** ****

' Based on code posted by user 'manish1239' in Xtreme Visual Basic Talk in
' October 2003 http://www.xtremevbtalk.com/archive/index.php/t-112708.html

' Coding notes: we send the 'Set PasswordChar' message to the textbox edit
' window in the VBA 'InputBox' dialog.  This isn't a straightforward task:
' InputBox is synchronous, a 'Modal Dialog' which leaves our application's
' VBA code in a waiting state at the exact moment we need to call the Send
' Message API function. So it runs by a delayed callback from an API Timer

' Warning: many of the 64-bit API declarations posted online are incorrect
' and none of them are correct for the pointer-safe Timer API Functions.

On Error Resume Next

SetTimer 0&, 0&, 10&, AddressOf TimerProcInputBox

InputBoxPassword = InputBox(Prompt, _
                            PASSBOX_INPUT_CAPTION, _
                            Default, _
                            XPos, YPos, _
                            HelpFile, HelpContext)

End Function

#If VBA7 And Win64 Then     ' 64 bit Excel under 64-bit windows  ' Use LongLong and LongPtr
                            ' Note that wMsg is always the WM_TIMER message, which fits in a Long
    Public Sub TimerProcInputBox(ByVal hwnd As LongPtr, _
                                 ByVal wMsg As Long, _
                                 ByVal idEvent As LongPtr, _
                                 ByVal dwTime As LongLong)
    On Error Resume Next

' REQUIRED for Function InputBoxPassword
' https://msdn.microsoft.com/en-US/library/windows/desktop/ms644907(v=vs.85).aspx

KillTimer hWndIbox, idEvent

Dim hWndIbox As LongPtr   ' Handle to VBA InputBox

hWndIbox = FindWindowEx(FindWindow("#32770", PASSBOX_INPUT_CAPTION), 0, "Edit", "")

If hWndIbox <> 0 Then
    SendMessage hWndIbox, EM_SETPASSWORDCHAR, Asc("*"), 0&
End If

End Sub


#ElseIf VBA7 Then          '  VBA7 in 32-Bit Office    ' Use LongPtr only

Public Sub TimerProcInputBox(ByVal hwnd As LongPtr, _
                             ByVal wMsg As Long, _
                             ByVal idEvent As LongPtr, _
                             ByVal dwTime As Long)
On Error Resume Next

' REQUIRED for Function InputBoxPassword
' https://msdn.microsoft.com/en-US/library/windows/desktop/ms644907(v=vs.85).aspx

Dim hWndIbox As LongPtr    ' Handle to VBA InputBox

KillTimer hwnd, idEvent

hWndIbox = FindWindowEx(FindWindow("#32770", PASSBOX_INPUT_CAPTION), 0, "Edit", "")


If hWndIbox <> 0 Then
    SendMessage hWndIbox, EM_SETPASSWORDCHAR, Asc("*"), 0&
End If


End Sub


#Else    ' 32 bit Excel

Public Sub TimerProcInputBox(ByVal hwnd As Long, _
                             ByVal wMsg As Long, _
                             ByVal idEvent As Long, _
                             ByVal dwTime As Long)
On Error Resume Next

' REQUIRED for Function InputBoxPassword
' https://msdn.microsoft.com/en-US/library/windows/desktop/ms644907(v=vs.85).aspx

Dim hWndIbox As Long    ' Handle to VBA InputBox

KillTimer hwnd, idEvent

hWndIbox = FindWindowEx(FindWindow("#32770", PASSBOX_INPUT_CAPTION), 0&, "Edit", "")

If hWndIbox <> 0 Then
    SendMessage hWndIbox, EM_SETPASSWORDCHAR, Asc("*"), 0&
End If


End Sub


#End If

You'll need the following declarations:

Option Explicit
Option Private Module

#If VBA7 And Win64 Then    ' 64 bit Excel under 64-bit windows   ' Use LongLong and LongPtr

Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
                                (ByVal hWnd1 As LongPtr, _
                                 ByVal hWnd2 As LongPtr, _
                                 ByVal lpsz1 As String, _
                                 ByVal lpsz2 As String _
                                 ) As LongPtr
Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _
                                (ByVal lpClassName As String, _
                                 ByVal lpWindowName As String) As LongPtr
Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" _
                                (ByVal hwnd As LongPtr, _
                                 ByVal wMsg As Long, _
                                 ByVal wParam As Long, _
                                 ByRef lParam As Any _
                                 ) As LongPtr
Private Declare PtrSafe Function SetTimer Lib "user32" _
                                (ByVal hwnd As LongPtr, _
                                 ByVal nIDEvent As LongPtr, _
                                 ByVal uElapse As Long, _
                                 ByVal lpTimerFunc As LongPtr _
                                 ) As Long
 Public Declare PtrSafe Function KillTimer Lib "user32" _
                                (ByVal hwnd As LongPtr, _
                                 ByVal nIDEvent As LongPtr _
                                 ) As Long


#ElseIf VBA7 Then     ' VBA7 in 32-Bit Office  ' Use LongPtr only, LongLong is not available

Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
                                (ByVal hWnd1 As LongPtr, _
                                 ByVal hWnd2 As LongPtr, _
                                 ByVal lpsz1 As String, _
                                 ByVal lpsz2 As String _
                                 ) As LongPtr
Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _
                                (ByVal lpClassName As String, _
                                 ByVal lpWindowName As String) As LongPtr
Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" _
                                (ByVal hwnd As LongPtr, _
                                 ByVal wMsg As Long, _
                                 ByVal wParam As Long, _
                                 ByRef lParam As Any _
                                 ) As LongPtr
Private Declare PtrSafe Function SetTimer Lib "user32" _
                                (ByVal hwnd As LongPtr, _
                                 ByVal nIDEvent As Long, _
                                 ByVal uElapse As Long, _
                                 ByVal lpTimerFunc As LongPtr) As LongPtr
Private Declare PtrSafe Function KillTimer Lib "user32" _
                                (ByVal hwnd As LongPtr, _
                                 ByVal nIDEvent As Long) As Long


#Else    ' 32 bit Excel

Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
                        (ByVal hWnd1 As Long, _
                         ByVal hWnd2 As Long, _
                         ByVal lpsz1 As String, _
                         ByVal lpsz2 As String _
                         ) As Long
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
                        (ByVal lpClassName As String, _
                         ByVal lpWindowName As String) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
                        (ByVal hwnd As Long, _
                         ByVal wMsg As Long, _
                         ByVal wParam As Long, _
                         ByRef lParam As Any _
                         ) As Long
Private Declare Function SetTimer Lib "user32" _
                        (ByVal hwnd As Long, _
                         ByVal nIDEvent As Long, _
                         ByVal uElapse As Long, _
                         ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" _
                        (ByVal hwnd As Long, _
                         ByVal nIDEvent As Long) As Long


#End If

Private Const PASSBOX_INPUT_CAPTION As String = "Password Required"
Private Const EM_SETPASSWORDCHAR    As Long = &HCC
Private Const NV_INPUTBOX           As Long = &H5000&

I posted this up on my blog, Excellerando, under the heading:

Asterisk the Galling: Using The VBA InputBox() For Passwords

.

As always,watch out for unwanted line breaks in the code.

Bookseller

The first form (#If VBA7 And Win64) calls KillTimer passing the unset hWndIbox instead of hWnd as happens in the other two variations – Proposal