VBA Input Pop up Boxes: Application.InputBox Method versus VBA.InputBox Function

[DocAElstein]

From last post…………..
Handleing of the MsgBox Pop up
The below attempts to put some clarity, as far as I am able, on what the handle of the MsgBox, or rather API User32 Windows dll MessageBoxA Function might be about. That MessageBoxA Function seems similar to the VBA Message box pop up, except that it is “non modal”, that is to say, when it is open, you can still select things outside it
I am able to find A “handle to a Window”, that is typically, the (typically optional) argument seen in API codings taking the form hWnd. It is a long number, given in a session. The number itself has no specific meaning, - although it may be produced by some specific process, its intended just to be a temporary number to give us the chance to reference a window at some time, such as when running a coding. By chance that number might be used again for something similar, but the odds are against it. Having that number, can, theoretically, for example, give you the chance to make something appear in a specific window. Apparently trying to get it can be a bit unreliable and inconsistent.
The next coding shows some experiments.

Option Explicit
‘
Private Declare Function APIssinUserDLL_MsgBox_NonModal Lib “user32” Alias “MessageBoxA” (Optional ByVal hWnd As Long, Optional ByVal Prompt As String, Optional ByVal Title As String, Optional ByVal buttons As Long) As Long ‘
Private Declare Function FindWndNumber Lib “user32” Alias “FindWindowA” (Optional ByVal lpClassName As String, Optional ByVal lpWindowName As String) As Long
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
’
Sub TestWndBreaks()    ‘    http://www.eileenslounge.com/viewtopic.php?f=18&t=28885#p223583   https://eileenslounge.com/viewtopic.php?f=18&t=41566&p=321874#p321874
Dim Response As Long
Rem 1 ’ Standard VBA Message Box
 Let Response = MsgBox(Prompt:=”Q_- Where am I, the MsgBox? “ & vbCrLf & “A_- Always in Excel spreadsheet”) ’
Rem 2 ‘ Message Box API User32 dll
 Let Response = APIssinUserDLL_MsgBox_NonModal(Prompt:=”Q_- Where am I, the MessageBoxA?” & vbCrLf & “(gave only  Prompt:=  argumant)”) ’
‘Stop ‘  So you can check running from spreadsheet window or VB editor, then get then say below, where it appeared
’Run from Spreadsheet  Spreadsheet
’Run from VB Editor    VBEditor


’ 2b) Get a number for hWnd to “lock” the Message box to a window
’ 2b)(i) Locked nowhere?
Dim WndNumber As Long
Let WndNumber = FindWndNumber(lpClassName:=vbNullString, lpWindowName:=vbNullString): Debug.Print WndNumber ’—must be vbNullString not “”  ??
Let Response = APIssinUserDLL_MsgBox_NonModal(hWnd:=WndNumber, Prompt:=”Q_- Where am I, the MessageBoxA?” & vbCrLf & “hwnd = “ & WndNumber & “”, Title:=”””Non Modal”” Pop Up”, buttons:=vbOKOnly) ’
‘Run from Spreadsheet  2032950 VBEditor  2032950 VBEditor  2032950 VBEditor
’Run from VB Editor  2032950 VBEditor
’Stop ‘ so as to say above the  hWnd  and say where the window was

’ 2b)(ii)
Let WndNumber = FindWndNumber(lpClassName:=”XLMAIN”, lpWindowName:=vbNullString): Debug.Print WndNumber ‘
Let Response = APIssinUserDLL_MsgBox_NonModal(hWnd:=WndNumber, Prompt:=”Q_- Where am I, the MessageBoxA?” & vbCrLf & “I used  lpClassName:=””XLMAIN””, lpWindowName:=vbNullString  to get  hWnd  which was “ & WndNumber & “”, Title:=”Experimanting with  lpClassName:=  “, buttons:=vbOKOnly) ’
‘Run from Spreadsheet   1115196 Spreadsheet
’Run from VB Editor     1115196 Spreadsheet
Stop ’ so as to say above the  hWnd  and say where the window was
End Sub

---

_-
I am proposing that using a variation of that can be used to write a short set of codes to allow the user to make a selection which can be shown as an address in the Pop Up which is the current collection, and which can be updated by making a new selection.
In this code although the Message box itself is not returning a range object, the final range returned at the end of the routine, Rsel , is based on the user selection. In addition the user on selecting “No” the Pop Up will re Pop up with the current selection displayed. That is effectively what the code snippets above do: Just to be clear again the similarity in _ the existing InputBox method, (coded to want a range object selection) and _ my pop up thing
_ when the existing InputBox method comes up you select the range you want and say OK. The end result usually is that some range object variable is set to the range you want
_ when my thing comes up, if the current selection(showing in the bar) is not what you want, you select what you want, and say “No”. That cause my thing to come up with the selection you want showing in the bar, so you say “Yes” . The end result usually is that some range object variable is set to the range you want

The end effect is something along the lines of a Pop Up User InputBox with range selection alternative to the Method _.....
Application.InputBox( Prompt:= , Title:= , Default:= , Left:= , Top:= , HelpFile:= , HelpContextID:= , Type:=8 )
_.......with API User 32 dll Programs.

Option Explicit
Private Declare Function APIssinUserDLL_MsgBox Lib “user32” Alias “MessageBoxA” (Optional ByVal hWnd As Long, Optional ByVal Prompt As String, Optional ByVal Title As String, Optional ByVal Buts As Long) As Long  ’                                                                               ‘ MessageBoxA   http://www.tek-tips.com/faqs.cfm?fid=4699
’
Public Sub PopUpInputBoxWithRngSelAPIUser32dll()
Noughty: ’PopUpInputBoxWithRngSelAPIUser32dll
Dim Rpnce As Long, Rsel As Range: Set Rsel = Selection
Dim Valyou As Variant: Let Valyou = Rsel.Value: If IsArray(Valyou) Then Valyou = Valyou(1, 1) ’For display Value of Top Left of Selection
 Let Rpnce = APIssinUserDLL_MsgBox(hWnd:=&H0, Prompt:=”Yes,  or No to ReCheck, Cancel for help “, Title:=”Selection Check: Address is “ & Rsel.Address & “  Value is “”” & Valyou & “”””, Buts:=vbYesNoCancel) ’ ‘ Pseudo Non Modal MsgBox
    If Rpnce = 2 Then Application.Help HelpFile:=ThisWorkbook.Path & “\AnyFileName.chm”, HelpContextID:=2 ’              -----    download this file:  https://app.box.com/s/bx2pkvtemsppscz60rd6f430wm89c6fj This is a “.chm Microsoft Help file” It has the name _ AnyFileName.chm ---  Put in same folder as this Workbook  ---   Check out possible workarounds  --- http://www.excelfox.com/forum/showthread.php/2146-%E0%A4%AC%E0%A5%8D%E0%A4%B2%E0%A5%89%E0%A4%97-%E0%A4%95%E0%A5%8B%E0%A4%B6%E0%A4%BF%E0%A4%B6-%E0%A4%95%E0%A4%B0-%E0%A4%B0%E0%A4%B9%E0%A4%BE-%E0%A4%B9%E0%A5%88-%D8%A8%D9%84%D8%A7%DA%AF%D8%B2-%DA%A9%DB%8C-%DA%A9*Trying-Blogs?p=10467#post10467   ---  xpu shopuld get this  HelpGetUpBollox.JPG https://imgur.com/KdKOYWr
If Rpnce = 7 Then GoTo Noughty ’ Option to update the displayed Address and Value in Top Left cell of that range
Set Rsel = Selection
End Sub

Public Sub PopUpInputBoxWithRngSelAPIUser32dllInXl()
Noughty: ’PopUpInputBoxWithRngSelAPIUser32dll
Dim WndNumber As Long: Let WndNumber = FindWndNumber(lpClassName:=”XLMAIN”, lpWindowName:=vbNullString): Debug.Print WndNumber ’
Dim Rpnce As Long, Rsel As Range: Set Rsel = Selection
Dim Valyou As Variant: Let Valyou = Rsel.Value: If IsArray(Valyou) Then Valyou = Valyou(1, 1) ’For display Value of Top Left of Selection
Let Rpnce = APIssinUserDLL_MsgBox_NonModal(hWnd:=WndNumber, Prompt:=”Yes,  or No to ReCheck, Cancel for help “, Title:=”Selection Check: Address is “ & Rsel.Address & “  Value is “”” & Valyou & “”””, Buts:=vbYesNoCancel) ’ ‘ Pseudo Non Modal MsgBox
If Rpnce = 2 Then Application.Help HelpFile:=ThisWorkbook.Path & “\AnyFileName.chm”, HelpContextID:=2 ’              -----    download this file:  https://app.box.com/s/bx2pkvtemsppscz60rd6f430wm89c6fj This is a “.chm Microsoft Help file” It has the name _ AnyFileName.chm ---  Put in same folder as this Workbook  ---   Check out possible workarounds  --- http://www.excelfox.com/forum/showthread.php/2146-%E0%A4%AC%E0%A5%8D%E0%A4%B2%E0%A5%89%E0%A4%97-%E0%A4%95%E0%A5%8B%E0%A4%B6%E0%A4%BF%E0%A4%B6-%E0%A4%95%E0%A4%B0-%E0%A4%B0%E0%A4%B9%E0%A4%BE-%E0%A4%B9%E0%A5%88-%D8%A8%D9%84%D8%A7%DA%AF%D8%B2-%DA%A9%DB%8C-%DA%A9*Trying-Blogs?p=10467#post10467   ---  xpu shopuld get this  HelpGetUpBollox.JPG https://imgur.com/KdKOYWr
If Rpnce = 7 Then GoTo Noughty ’ Option to update the displayed Address and Value in Top Left cell of that range
Set Rsel = Selection
End Sub

So the code simply takes the current spreadsheet selection as the required range. The address of this is also displayed as the Pop up caption.
An option is included for a help file, ( I have checked and found that this works in excel 2003, 2007 and 2010 for a valid .chm file)
An Option is also included to repeat the process. ( This allows a new selection to be displayed in the caption)
The end effect is something along the lines of a Pop Up User InputBox with range selection alternative to the Method_..
Application.InputBox( Prompt:= , Title:= , Default:= , Left:= , Top:= , HelpFile:= , HelpContextID:= , Type:=8 )
_....... with API User 32 dll Programs.

Just to say it again: Just to be clear again the similarity in _ the existing InputBox method, (coded to want a range object selection) and _ my pop up thing
_ when the existing InputBox method comes up you select the range you want and say OK. The end result usually is that some range object variable is set to the range you want
_ when my thing comes up, if the current selection(showing in the bar) is not what you want, you select what you want, and say “No”. That cause my thing to come up with the selection you want showing in the bar, so you say “Yes” . The end result usually is that some range object variable is set to the range you want




Further reading shows that an even more “fundamental” API Function is the MessageBoxTimeoutA. It can be used similarly, and indeed might be an even better option as it used by all other Message Box Functions. Therefore it might be a good option as it is more likely to be maintained in the future ( https://www.excelforum.com/developme...ml#post4822413 )



The next posts tackle the Window positioning ( and size ) issue, Rem2_b)(ii) the positional arguments

_._______________________________


_._______________________________