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.