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

**DocAElstein** · 02-09-2018, 01:34 AM

Pop Up User pseudo InputBox with range selection alternative with API User 32 dll Programs.

This is a summary final working codes solution to allow a user to use a simple Pop up ( non UserForm ) to make an Excel range object selection.
This solution allows you to make a spreadsheet selection whilst the Pop Up is up.
The Standard stuff currently available
The VBA Message Box and VBA Input Box Functions are Modal, in other words you cannot do anything to the spreadsheet when they are up.
The Application Input Box Method should allow you to do this when you choose the last option as , Type:=8. It does allow you to do this, but a couple of things are broken:
_ The ability to position the Pop up ( appears to be broken since Excel 2007 )
_ The Microsoft help function does not appear to me to work in Excel 2003 2007 2010. I do not know if it ever worked for the Application Input Box Method
My Solution
This solution overcomes these problems, which is the main reason I did it, especially because of the first problem. It also has a few extra things that might be useful
_ You can choose the size of the Pop up ( width , height )
_ You can adjust the “z” things… I am not too clear on these options but in simple terms it means that you arrange how it appears in terms of the order of what windows you see, how and in which priority you see it, what windows are “under” or “above” it to see etc..
_ A simple change of the ByRef to ByVal in the signature line of a Called routine ( Sub HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp(By___ ) allows you to change the value of a range object to that of the selection, but the original range object will not change, that is to say its address remains as before the selection. That could give you an extra option in how you select and move around in a spreadsheet.

All codes should be copied to the same code module.

The first sections Rem 1 and Rem 2 makes the necessary API programme available and declares (Dim’s) a couple of related globial variables. This section will need to go at the top of a code module.
Rem 1 is straight forward and makes available a pseudo Non Modal message box.
Rem 2 is a bit more complicated and makes available a few API program things needed to mess about with Windows dimensions when they come up.

Code:

Option Explicit    '                                                                                                    “Window"s is a name for a programming idea which might result in something we “see” as what we conceive as Windows. Manipulating of the actual “Windows” seems the key to pseudo “making my own” InputBox with range selection.        Direct linked libraries (dll) are available to run as and when required, hence the wording of direct link: They are used as an efficient means to organise Microsoft’s software generally allowing different Applications to share smaller programs which are shipped as standard with the Microsoft Windows Operating system. They are however also available to programmers , programming the applications.  They are usually contained in Folder with name similar to User 32. "API calls”: just means usually that you are using those things and related “Windows” concept-all gets gets bundled up in imprecise intimidating term API, for Application Programming interface
Rem 1 Pseudo Non Modal MsgBox,   MessageBoxA          API Standard Non Standard Stuff, More Fundamentally complicated    UnWRap it and.. "Pseudo Non Modal MsgBox"           ---  A valid handle, hWnd,  other than the Excel spreadsheet window (  Private Declare Function FindWndNumber Lib "user32" Alias "FindWindowA" (Optional ByVal lpClassName As String, Optional ByVal lpWindowName As String) As Long --- hWndParent = FindWndNumber(lpClassName:="XLMAIN", lpWindowName:=vbNullString)   ),  or even no ( Null ) hWnd results in a pseudo Non Modal MsgBox                                                                                http://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=10476#post10470      http://www.tek-tips.com/faqs.cfm?fid=4699
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  '
'_- ====  The above is all I need to do so that writing  APIssinUserDLL_MsgBox  in any code in this code module will do something very similar to the VBA  MsgBox.  The main difference is that when it is up, I can still scroll up and down in my Excel Spreadsheet and also select a range.
Rem 2_b)(ii) == To set/change The positional arguments        "Sub Classing a "Window""       As is generally the case with “Window” Functions, A window belongs to a class. The Dynamic Linked Libraries concept allow the small programs in the with windows shipped typically in the User32 Folder programs to be called up / used at runtime, rather than a fixed set of instructions copied or and/ or used as such at some point. This allows for a modification of the class, known as Sub classing. This means that it is possible to modify / add to the “Window” Function and so pseudo create a customised ddl. It does not necessarily mean that a “Window” Function or a used User32 Folder program is directly Sub classes , but it just happens to be in our case as we are intending to mess about with the MessageBoxA ( or MessageBoxTimeoutA )  You can arrange that a used “Window” Function is modified as it is used.
' The next four line will tie something on my chain for when you pull it.                     Similar in the way that a Worksheet_change code is triggered as something happens, you must arrange that a VBA Function is triggered when a Windows “event” occurs. At this point the concept gets a bit vague and I doubt many people really understand anymore how it really works. A good name for the VBA Function might be Function WinSubWinCls_JerkBackOffHooKterd. This VBA Function will itself be a pseudo “Window” Function and “hung” or hooked on a chain of events. Because of the dynamic / volatile nature of the stuff, things will have a habit of going on forever if they not “unhooked” such that a procedure will have to be designed to unhook itself.

**DocAElstein** · 02-09-2018, 02:19 AM

Page 2 https://www.excelfox.com/forum/showt...Function/page2
Original post #11, #post10483

Section 3a) would be your main code in which you wanted to use / call up the Pop Up User pseudo InputBox with range selection thing which is the main issue of the last few posts.
The simple demo I have done helps illustrate that thing I mentioned about the change of the ByRef to ByVal. If you play around with the code and change that __(By___) at the start of the next code section, then I think you will get the point of what I am suggesting there
So in section 3a) the main thing related to the issue is the line
_ Call HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp(RSel)
That code line makes the pop up come up, and the selected spreadsheet range is returned as a range object in the variable RSel. The code will wait until you have made the selection, but it will not prevent you from making the selection. So in that respect it works similarly to the Application Input Box Method when you use that Application Input Box Method and choose the last option as , Type:=8.

Code:

'  ========================
Rem 3a) This is just to demo the idea of a Pop Up User InputBox with range selection alternative with API User 32 dll Programs.      ' Normally in this section 3a) there would be other stuff and probably lots of it and if I have anything then it will be very Pretty.. Pretty well disgusting probably.
Sub MainSubWithAllOtherStuffInIt() ' This would be your main coding and would nornally be a lot bigger, it is just here as part of the demo for a Pop Up User InputBox with range selection alternative with API User 32 dll Programs
' Some where in the main code I might want to ask the user to select a range. So to do that I
Dim RSel As Range ' This is a variable to hold the Pointer to the users range object..        So this variable in VBA is like the Link to the part of a URL string reducing size site where a few things about the actual Final site is informed about. This area in that site, like a pigeon Hole to which the variable refers, ( the "pigeon hole" location address, and all its contents would be defined as the "Pointer". Amongst other things it has a link, a "Pointing part", pointing to actually where all the stuff is
 'Set RSel = Selection ' This line will be needed if you chose to send ByVal. That is necerssary to ensure that you have a range object - If you do not have a range object when you go to HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThatMsgBoxUp(ByVal RcelsToYou), then you wont have one when you get back neither, as in HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThatMsgBoxUp you will be Set ing the copy variable, not the actual RSel variable. You put a copy of the Pointer in the new variable. But it is an object. A different object. A Copy object.   https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it-2.html#post4386360
 Call HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThatMsgBoxUp(RSel)  '                     In a normal application of the main Theme of all this, this would be the main code line you use to cause a the "Pop Up User pseudo InputBox with range selection alternative with API User 32 dll Programs"
 VBA.MsgBox Prompt:="Address check RSel - It is now " & RSel.Address & "" & vbCrLf & "Da .Value of the range object is " & RSel.Value ' Just done to demo that A simple change of the ByRef to ByVal in the signature line of a Called routine allows you to change the value of a range object to that of the selection, but the original range object will not change, that is to say its address remains as before the selection.
End Sub

**DocAElstein** · 02-09-2018, 02:37 AM

This is now post 15 after I copied all of full page 2 to get a full page 3
It was Original post #12, it got shifted down one to post #13 when the original posts 11 12 and 13 where copied on 31 Oct 2024, so it got edited to have the contents of the original post # 13
#post10484

It is Original post #13, it got shifted down to post #15 when posts 11 12 and 13 where copied on 31 Oct 2024 #post10485

The bit appearing in your main main coding. The bit you run to do the final thing

Rem 3a) This is just to demo the idea of a Pop Up User InputBox with range selection alternative with API User 32 dll Programs. ' Normally in this section 3a) there would be other stuff and probably lots of it and if I have anything then it will be very Pretty.. Pretty well disgusting probably.
Sub MainSubWithAllOtherStuffInIt() ' This would be your main coding and would nornally be a lot bigger, it is just here as part of the demo for a Pop Up User InputBox with range selection alternative with API User 32 dll Programs
' Other stuff

' Some where in the main code I might want to ask the user to select a range. So to do that I

The next bit is what you actually need in a program. Everything before and after this would likely be in a separate module, likely named something like InpBxAPIddllWindowsSubClassing ( The Purple stuff is an extra demo you would not noremally have. )
So it is just two lines usually

Code:

Dim RSel As Range ' This is a variable to hold the Pointer to the users range object..        So this variable in VBA is like the Link to the part of a URL string reducing size site where a few things about the actual Final site is informed about. This area in that site, like a pigeon Hole to which the variable refers, ( the "pigeon hole" location address, and all its contents would be defined as the "Pointer". Amongst other things it has a link, a "Pointing part", pointing to actually where all the stuff is
 'Set RSel = Selection ' This line will be needed if you chose to send ByVal. That is necerssary to ensure that you have a range object - If you do not have a range object when you go to HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThatMsgBoxUp(ByVal RcelsToYou), then you wont have one when you get back neither, as in HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThatMsgBoxUp you will be Set ing the copy variable, not the actual RSel variable. You put a copy of the Pointer in the new variable.  https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it-2.html#post4386360    https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing?p=24214&viewfull=1#post24214    
 Call HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThatMsgBoxUp(RSel)  '                     In a normal application of the main Theme of all this, this would be the main code line you use to cause a the "Pop Up User pseudo InputBox with range selection alternative with API User 32 dll Programs"
 'VBA.MsgBox Prompt:="Address check RSel - It is now " & RSel.Address & "" & vbCrLf & "Da .Value of the range object is " & RSel.Value ' Just done to demo that A simple change of the ByRef to ByVal in the signature line of a Called routine allows you to change the value of a range object to that of the selection, but the original range object will not change, that is to say its address remains as before the selection.

' Other stuff

End Sub ' Typically this is your main program End Sub

**DocAElstein** · 02-09-2018, 02:48 AM

This is now post 19 after I copied all of full page 2 to make a full page 3
It looks like this link is broke, https://www.excelfox.com/forum/showt...ll=1#post10485 as it takes you to page 3 , the link made from the page 2 link , https://www.excelfox.com/forum/showt...age2#post10485 is OK

Archive post, one I updated later. Just kept in case I need it later (Was original post # 4)
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.
I am able to find A “handle to a Window” that makes the API User32 Windows dll MessageBoxA appear to work as the Standard “non modal” VBA MsgBox.
In all other cases of either a successfully found handle number ( to which it belongs is not clear to me ) or an unsuccessful found handle number ( hWnd is then 0 as returned from FindWind___ ), I appear to have a “non modal” Pop up box, in which case I have the possibility to make a spreadsheet selection with the Pop Up, popped up
Some further reading has suggested that the unsuccessful found handle number returns a specific type of Long Null and contradictorily to some other literature suggests that this pop up must not have a owner window. There may be some morae subtle points to it, but for now the use of the special symbol for a Long Null _ &H0 _ would suggest that the major part of the solution can be reduced to a simplified code lines such as in '2e) This will do then

Code:

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 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
Dim Response As Long
Rem 1 ' Standard VBA Message Box
 Let Response = MsgBox(Prompt:="Q_- Where am I, the MsgBox? " & vbCrLf & "A_- locked Middle in Excel spreadsheet") '
Rem 2 ' Message Box API User32 dll
 Let Response = APIssinUserDLL_MsgBox(Prompt:="Q_- Where am I, the MessageBoxA?" & vbCrLf & " A_- Middle in Active Window") '
' 2b) Get a number for hWnd to "lock" the Message box to a window
' Locked nowhere?
Dim WndNumber As Long
 Let WndNumber = FindWndNumber(lpClassName:=vbNullString, lpWindowName:=vbNullString): Debug.Print WndNumber ' -- must be vbNullString not ""  ??
 Let Response = APIssinUserDLL_MsgBox(hWnd:=WndNumber, Prompt:="Q_- Where am I, the MessageBoxA?" & vbCrLf & " A_- Middle in Active Window, hwnd = " & WndNumber & "", Title:="""Non Modal"" Pop Up", buttons:=vbOKOnly) '

' 2c) working like the Standard VBA Message Box ??        https://www.techrepublic.com/blog/10-things/10-plus-of-my-favorite-windows-api-functions-to-use-in-office-applications/
Dim hWndParent As Long
 Let hWndParent = FindWndNumber(lpClassName:="XLMAIN", lpWindowName:=vbNullString): Debug.Print hWndParent '
 Let Response = APIssinUserDLL_MsgBox(hWnd:=hWndParent, Prompt:="Q_- Where am I, the MessageBoxA?" & vbCrLf & " A_- locked Middle in Excel spreadsheet, hwnd = " & hWndParent & "", Title:="""Working like"" Microsoft Excel", buttons:=vbOKOnly) '
' 2d) Not sure whats gooing on
 Let hWndParent = FindWndNumber(lpClassName:="XLMAIN", lpWindowName:=ThisWorkbook.Name): Debug.Print hWndParent '
 Let Response = APIssinUserDLL_MsgBox(hWnd:=hWndParent, Prompt:="Q_- Where am I, the MessageBoxA?" & vbCrLf & " A_- locked Middle in Active Window??, hwnd = " & hWndParent & "", Title:="""Working like"" Non Modal ??", buttons:=vbOKOnly) '
 Let hWndParent = FindWndNumber(lpClassName:=vbNullString, lpWindowName:=ThisWorkbook.Name): Debug.Print hWndParent '
 Let Response = APIssinUserDLL_MsgBox(hWnd:=hWndParent, Prompt:="Q_- Where am I, the MessageBoxA?" & vbCrLf & " A_- locked Middle in Active Window??, hwnd = " & hWndParent & "", Title:="""Working like"" Non Modal ??", buttons:=vbOKOnly) '
Dim hWndDskTop As Long
 Let hWndDskTop = FindWindowEx(hWndParent, 0&, "XLDESK", vbNullString): Debug.Print hWndDskTop '  https://msdn.microsoft.com/de-de/library/windows/desktop/ms633500(v=vs.85).aspx
 Let Response = APIssinUserDLL_MsgBox(hWnd:=hWndDskTop, Prompt:="Q_- Where am I, the MessageBoxA?" & vbCrLf & " A_- locked Middle in Active Window??, hwnd = " & hWndDskTop & "", Title:="""Working like"" Non Modal ??", buttons:=vbOKOnly) '
 Let hWndDskTop = FindWindowEx(hWndParent, 0&, "XLDESK", ThisWorkbook.Name): Debug.Print hWndDskTop
 Let Response = APIssinUserDLL_MsgBox(hWnd:=hWndDskTop, Prompt:="Q_- Where am I, the MessageBoxA?" & vbCrLf & " A_- locked Middle in Active Window??, hwnd = " & hWndDskTop & "", Title:="""Working like"" Non Modal ??", buttons:=vbOKOnly) '
'2e) This will do then
 APIssinUserDLL_MsgBox hWnd:=&H0, Prompt:="This will do", Title:="""Working like"" Non Modal" '
End Sub

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 a t 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

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

Code:

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

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.

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

_._______________________________

_._______________________________