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

[DocAElstein]

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

Hi
Recently I have been trying to revise my knowledge of various ways to get simple user interaction with simple Pop ups. ( Later I will go into the “custom route” and learn about UserForms )
I started at a fairly ignorant level of knowledge and I thought the notes that I made for myself could be useful for someone learning about these things, so I am sharing them here. I have noticed some problems / Bug “features” , so I might try to extend the notes later to try to share an alternative semi – self made solution using the available collection of supplied with Windows small additional programs ( API, User 32 dll stuff ) that can also be accessed in a “user customerised” way in VBA.
But Initially , I am talking about these two things:
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-inputbox-method-excel
https://msdn.microsoft.com/en-us/library/office/aa195768(v=office.11).aspx
https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/inputbox-function
https://powerspreadsheets.com/excel-vba-inputbox/ )

Bug Features
The comparison of these two similar things is complicated by various Bugs in Application.InputBox . Often when one gets caught out for the first time by such a Bug , then one learns of the two similar alternatives available in VBA to get an Input Pop up Box

I have never quite got a clear understanding or definition of the difference between a Method and a Function in these things. However in this case that is probably the key to understanding the difference.
My conclusion at the end is that the Application.InputBox Method is something of an abortion, and I hope to give later a simple alternative to one of its most popular uses, that of returning the range object of a selection made by the user in a worksheet.

Preparations Some preliminary stuff. ( Help File )
To help test check and understand the differences it is necessary to do some preliminary stuff which due to other Microsoft Bug features may not work. :( Never mind.
Follow the following instructions. You need to get a “working” Microsoft help type Window to pop up. If you cannot get it to work then never mind: It just means that you will have to forgo on a demo of one of the features.
So, here we go… Prepare a new File, or use a spare one. Important is that you save it as .xls, not .xlsm. It would be useful if you have access to two Excel versions:
Excel 2003 or earlier
and
Excel 2007 or later.
If not, no worries – you will just have to forgo on a few other experiments.

Having got a File saved somewhere ( it does not matter where it is saved ), then download this file:
https://app.box.com/s/bx2pkvtemsppscz60rd6f430wm89c6fj This is a “.chm Microsoft Help file” It has the name _ AnyFileName.chm
Important is to save it in the same folder as your file. Try and open that file
either
_1 directly, if your browser gives you the option, example for Google Chrome: GetOpen_1_2.JPG https://imgur.com/gRjn0HJ https://i.postimg.cc/3wt0t6PP/Open-downloaded-chm-help-file.jpg
Or
_2 by double clicking on the file in Windows Explorer https://i.postimg.cc/cHmmdXtW/Double-Click-on-File-in-Windows-Explorer.jpg GetOpen_1_2.JPG https://imgur.com/gRjn0HJ
_3 Or another typical short cut way you know to open a file, etc.

Or
_3 Use this code

Sub HelpGetItUp_3()
Application.Help HelpFile:=ThisWorkbook.Path & "\AnyFileName.chm", HelpContextID:=2
End Sub
Click on the Help Button, You should see this:
HelpGetUpBollox.JPG https://imgur.com/KdKOYWr https://i.postimg.cc/tT7Zwkb7/Help-Button-on-Input-Box.jpg

If that does not work… you could try to sort it out based on the information here: https://www.excelforum.com/excel-new-users-basics/1099015-vba-application-inputbox-option-helpfile-helpcontextid.html#post4827566
If you can not get that to work, then no worries – you will just have to forgo on a few other experiments.

InputBox Method and Function. Method or Function
I think loosely a Function is defined as some sort of process, Sub code , set of instructions or some pre defined way of doing something. In normal life a Method is just another word for that. In programming the word Method might loosely be described as a Function “within” an Object and/ or a Function “using” things in an object. There lies the crux of the difference.
So if there is any truth to that last explanation, then the basic or more fundamental or more “lower level” of the two things can thought of as being the….
Input Box Function.
This returns a string of what the user types into the Pop up box which should come up when you use a code of this form:
Dim strReturned As String
_ Let strReturned = InputBox(Prompt:="Type Something in", Title:="MyBox", Default:="Something", xpos:=100, ypos:=100, HelpFile:=ThisWorkbook.Path & "\AnyFileName.chm", Context:=2)
A code like that should give something like this:
InputBoxFunctionWithHelpButton.JPG https://imgur.com/htritBi https://i.postimg.cc/tgqVtym3/Type-something-in-your-Input-Box.jpg https://i.postimg.cc/tTny1qxR/Input-Box-Something.jpg


As far as I know all is well understood and works with the VBA InputBox Function.

One thing worth having a quick play around with at this stage is those x y positional numbers
It should result in the box coming up in different places. In the next post I have a demo code, and the first short part, Rem 1 , has those code lines. So.. open your .xls file in Excel, hit Alt+F11 to get into the VB Editor, paste in that code into the large Code window, then run the code, preferably in Debug Mode, F8, for this initial selection. Then you can hit the stop button ( VBEditorStopButton.JPG https://imgur.com/0o8Ojqm https://i.postimg.cc/rwCrrzgy/Stop-Button-VB-Editor.jpg ), edit those positional number values, the re run the code.
_._______________________

For Input Box Function When that is showing, you do not have any chance to select anywhere in the spreadsheet. You simply have to enter a string value into the Pop up Input Box Input Bar. The technical term here is along the lines of “The VBA InputBox Function is always Model” . In English that means you can’t do anything else in the worksheet until you either close the pop up Input Box or Hit the OK Button. Effectively the Excel Window is “hung up”, or “control is passed” to the Pop up Box
This is perhaps a very important point , particularly in discussions of the difference in the two

Application Input Method
From now on it is a bit of a complicated mess because of Bug features.
The basic idea is that within the object that is the current instance of the (Excel in this case ) object ( known loosely as the “Application” ) that the VBA InputBox Function is used somehow. That is to say , one might imply that the Application Input Method somehow uses the VBA InputBox Function “within it”, that is to say it is using for it data / information from the Application object.
I doubt anyone knows much better then that description for sure.
And I question whether there is any truth at all to that.
Clearly who ever “invented” it doesn’t know what is going on. If he or she did, then it might work consistently or the Bug features which have been around for about 10 years might have been fixed. I only have Excel 2003, 2007 and 2010. As far as I can see the Bug features started at Excel 2007 for the Positional arguments, and are still there in Excel 2010. If anyone following this and trying the experiments on a more recent version can add anything here then that would be welcome.
But the Help option arguments do not appear to work in any version for the Application.Input Box Method. So probably the Application Input Method was flaky right from the start and got worse as time went on.

Very briefly for now, and just as a quick approximate statement: The difference in the two things is that the Method has an extra last optional argument and if that is included then what is actually done is a bit more complicated ( I would suggest it is a mess actually – hence someone messed up and that causes the Bug features ).

Before we go full out in using the Application Input Method, consider the following that is often seen in Microsoft documentation:
……. that Application.InputBox calls the InputBox method; InputBox with no object qualifier calls the InputBox function.
I don’t think this makes any sense, or at least it is very unclear.
One reasonable interpretation is that the box that comes up probably does not have a lot to do with the VBA InputBox Function once the last optional argument of type is given. Or visa versa, a reasonable interpretation of that might reasonably be that without the extra last optional Type:= argument then effectively the InputBox function is done.
A short bit of experimenting suggest however that in any situation ( that is to say any argument selection used, even the minimum of just the first ( only not optional ) Prompt:= ) , something significantly different is going on with the Application.InputBox Method compared to the VBA InputBox Function.

Regardless of whether the last argument, Type:= , is given or not, clearly what happens is significantly different in the case of Application.InputBox Method compared to VBA InputBox Function

I did some experimenting, and my observations are that for any options combination you have, with Application.InputBox Method , an additional possibility to add data other than just entering into the Box input bar: You can select the spreadsheet: Although the code that fires up the Application.InputBox Method “waits” , it is possible to make a selection in the spreadsheet. (The Application.InputBox is "Non-Modal"). If a selection is made then the address is shown in the absolute column letter and row number notation ( Like $A$1:$B$6 etc.. ) appears automatically in the pop up box input bar. It would appear that some coding determines what to return based on the area selected and the Type:= . ( If no Type:= is given the default type is taken as Text (a string), ( but as noted, the Application.InputBox Method does not then simply work as if it were the VBA InputBox Function )

A full code is given in the next post in which a section, Rem 3 , loops through types allowing an input for each type to be made. ( As is standard for the Application.InputBox Method you can make an entry by typing into the pop up box input bar, or make a spreadsheet selecting in which case the pop up box input bar is automatically filled with the selection address in the absolute column letter and row number notation.
Note that for the case of a returned range object, there will not be an error in assigning the returned thing to a variant ( Let Var = , or Var =) , ( rather than a more conventional in this case of Setting it to a range object) , as this is a typical case where VBA returns the default property of .Value from the range object. However the code has to do a bit of juggling about for this return and in the case of an Array return in order to show those values in a single displayable String

After selecting OK , a message box attempts to show what is actually returned from the Application.InputBox Method.
( If you have any difficulties with the help optional arguments_...
HelpFile:=ThisWorkbook.Path & "\AnyFileName.chm", Context:=2
or
HelpFile:=ThisWorkbook.Path & "\AnyFileName.chm", HelpContextID:=2
-…. Then if you cannot get the workarounds ( https://www.excelforum.com/excel-new-users-basics/1099015-vba-application-inputbox-option-helpfile-helpcontextid.html#post4827566 ) to work then simply remove them and ignore the help stuff )

_.______________

My guess is that there is a messy routine full of bug features which looks initially
either
at what is inputted by the user in the Input Box Input Bar,
or
if a selection is made by the user in a worksheet, the address is placed into the Input box, and a note is made of the actual range object that is referred to by the selection.

Thereafter an attempt is made to give a Type correct return. Depending on exactly what is inputted, this may or may not be accepted. This last point is reasonable I think.
As example: if type Number is specified in the options ( ____, Type:=1 ) _ ‘ Number ) then
an error occurs if a string is inputted or if a cell is referenced which has a string in it. A reference to an empty cell will result in _ 0 _ for this case. This is consistent with assigning a Number variable to the .Value of a range object.
If no string is entered into the Input Box Input Bar then Application.InputBox Method issues a pop up warning that an incorrect entry has been made. You are able then to correct this by adding a correct entry. It is not clear from this exactly what process then follows, but clearly it is a bit different to the user worksheet area selection case.
For all but the Type:= range and Type:= array , and Type:= formula, a multi cell selection results in just the top left cell being considered.


A full listing of what happens for different entries for each type would take a book. I might do that later. For now it is probably best to experiment with the code for the thing you are trying to do and variations thereof. Better still, don’t use the Application.InputBox Method as it is too flaky / full with bug features.

In the over next post I may try some alternatives, but not yet the UserForm. For now I am interested in the more standard/ simpler alternatives.

Demo code in next post and a few other places possibly: ( https://www.excelforum.com/development-testing-forum/1215283-gimmie-ta-codexamples-call-in-the-appendix-posts-2018-no-reply-needed-but-if-u-2.html#post4829620
https://pastebin.com/58U8eagx )

References in the next post also.

[DocAElstein]

' https://support.microsoft.com/de-de/help/199824/how-to-get-the-address-of-variables-in-visual-basic ' http://vb.mvps.org/tips/varptr/ ' https://www.mrexcel.com/forum/excel-questions/35206-test-inputbox-cancel-3.html
'
' ( Ctrl+Pause will usually stop this program ) https://www.excelforum.com/development-testing-forum/1215283-gimmie-ta-codexamples-call-in-the-appendix-posts-2018-no-reply-needed-but-if-u-2.html#post4828681
Sub UpInputBox() ' Note that you should make the Preparations here: ' https://www.excelforum.com/excel-new-users-basics/1099015-vba-application-inputbox-option-helpfile-helpcontextid.html#post4827572 ' https://www.click2trial.com/sure-thin-garcinia/
Rem 1 ' VBA Input Box Function works in all Excel versions so far. ' Application.Help HelpFile:="G:\Excel0202015Jan2016\ExcelForum\UserForm\HTML Workshop\chmFillesProjectFiles\Jan21\AnyFileName.c hm", HelpContextID:=2
Dim strReturned As String ' "Pointer" to a "Blue Print" (or Form, Questionaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular “Value”, or (“Values” for Objects). There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. A String is a a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quich checks.. But..http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
Let strReturned = InputBox(Prompt:="") ' This is the minimum option. Here and in the next line you can only put infomation in the box bar
If StrPtr(strReturned) = 0 Then Exit Sub ' If OK is clicked with no entry then a zero length string is returned. If Cancel is selected then the string variable does not yet have an address https://www.mrexcel.com/forum/excel-questions/917689-passing-array-class-byval-byref.html#post4412382 https://www.mrexcel.com/forum/excel-questions/35206-test-inputbox-cancel-2.html?highlight=strptr#post2845398
Dim strReturned2 As String ' ' http://www.excelfox.com/forum/showthread.php/1828-How-To-React-To-The-Cancel-Button-in-a-VB-(not-Application)-InputBox
Let strReturned2 = InputBox(Prompt:="Type Something in", Title:="MyBox", Default:="Something", xPos:=1000, yPos:=1000, HelpFile:=ThisWorkbook.Path & "\AnyFileName.chm", Context:=2)
If StrPtr(strReturned2) = 0 Then Exit Sub
Rem 2 ' Application Input Box Method with limited used optiional parameters
Dim VarReturn As Variant ' Use Variant so as to allow for whatever return that the Application Input Box Method may return https://msdn.microsoft.com/en-us/library/office/gg251422.aspx
Let VarReturn = Application.InputBox(Prompt:="") ' This is the minimum, BUT ALREADY we can now select a range in the worksheet
Let VarReturn = Application.InputBox(Prompt:="", HelpFile:=ThisWorkbook.Path & "\AnyFileName.chm", HelpContextID:=2) ' In Excel 2007+ the help options do not work. Nor do they in Excel 2003
Let VarReturn = Application.InputBox(Prompt:="", HelpFile:="Any Fink you like ", HelpContextID:=346326) ' In Excel 2007+ the help options appear not to be referrenced. Nor do they in Excel 2003
Let VarReturn = Application.InputBox(Prompt:="", Left:=100, Top:=100) ' In Excel 2007+ the positional arguments have no effect and the Input box comes up where it was the last time it came up. In Excel 2003 the positional arguments appear to work

Rem 3 ' Full options in Application InputBox
Dim dicLookupTableMSRD As Object ' For 7 type options in last Optional argument ( and 1 example of a combination )
Set dicLookupTableMSRD = CreateObject("Scripting.Dictionary") 'Late Binding MSRD In this case Dictionary and Scripting.Dictionary are the same. You can be sure of that because removing the reference to the Scripting runtime makes the Dictionary code fail. When you declare a variable as Dictionary, the compiler will check the available references to locate the correct object. There is no native VBA.Dictionary incidentally, though it is of course possible to create your own class called Dictionary, which is why I used the phrase "in this case". https://www.excelforum.com/excel-pro...ml#post4431231 http://www.eileenslounge.com/viewtop...=24955#p193413 https://www.excelforum.com/excel-pro...d-formats.html http://advisorwellness.com/blue-fortera/
Let dicLookupTableMSRD.CompareMode = vbTextCompare ' Compare mode property vbTextCompare is case unnsensitive. This must be done at the point here that the dictionary is empty
dicLookupTableMSRD.Add Key:=0, Item:="Formula" ' if you make a selection it
dicLookupTableMSRD.Add Key:=1, Item:="Number" ' Like a Double, Long, Single etc.
dicLookupTableMSRD.Add Key:=2, Item:="Text (a String)"
dicLookupTableMSRD.Add Key:=1 + 2, Item:="Number or Text" ' An example of one possible combinination
dicLookupTableMSRD.Add Key:=4, Item:="Logical value (True or False)" ' A logical value (True or False)
dicLookupTableMSRD.Add Key:=8, Item:="Range object" ' Note: usually you would then Set=InputBox for this option only. However, for the case of a returned range object, there will not be an error in assigning the returned thing to a variant ( Let Var = , or Var =) , ( rather than a more conventional in this case of Setting it to a range object) , as this is a typical case where VBA returns the default property of .Value from the range object. However the code has to do a bit of juggling about for this return and in the case of an Array return in order to show those values in a single displayable String
dicLookupTableMSRD.Add Key:=16, Item:="Error value" ' An error value, such as #N/A
dicLookupTableMSRD.Add Key:=64, Item:="Array"
Dim TypeOptions() As Variant: Let TypeOptions() = dicLookupTableMSRD.keys ' This Method returns an Array in Variant types - The variant type is necerssary as those keys could have been almost anything apart from Arrays. So we had to declare the Array to get the stuff back Variant type
Dim Stear As Variant
For Each Stear In TypeOptions()
Dim ThingReturned As Variant
Let ThingReturned = Application.InputBox(Prompt:="Type " & CLng(Stear) & ", " & dicLookupTableMSRD.Item(Stear) & "", Title:="MyBox", Default:="Something", Left:=100, Top:=100, HelpFile:=ThisWorkbook.Path & "\AnyFileName.chm", HelpContextID:=2, Type:=CLng(Stear))
If Not Stear = 4 And ThingReturned = False Then Exit Sub ' We can do a check here for if the user hit Cancel for all but the Type:= logical value as a logical value of false is returned for if the cancel is selected
If IsArray(ThingReturned) Then ' We have a 2 dimensional array even in the case of a single row or single column as that is how VBA holds array returned from a spreadsheet area
Dim strThingsReturned As String: Let strThingsReturned = "" ' If this is not reset to "" then the string build for the second time ( the Array type ) will include the first string built von the Range, that is to say the Range defaulting to Range.Value 'Let strThingsReturned = VBA.Strings.Join(ThingReturned, ", ")This is no good to us as the first argument must be a 1 dimension Array
Dim rIndx As Long, cIndx As Long ' Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in. '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. ) https://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop-4.html
For rIndx = 1 To UBound(ThingReturned, 1)
For cIndx = 1 To UBound(ThingReturned, 2)
Let strThingsReturned = strThingsReturned & ThingReturned(rIndx, cIndx) & ", "
Next cIndx
Let strThingsReturned = VBA.Strings.Left$(strThingsReturned, (Len(strThingsReturned) - 2)) ' Remove the last ", " in each complete row
Let strThingsReturned = strThingsReturned & ";" & vbCrLf ' go down a line for next row
Next rIndx
Let strThingsReturned = VBA.Strings.Left$(strThingsReturned, (Len(strThingsReturned) - 3)) ' takes off the last vbCr & vbLf & ";"
Debug.Print strThingsReturned: MsgBox Prompt:="Returned using option Type:=" & Stear & " ( " & dicLookupTableMSRD.Item(Stear) & ")" & vbCrLf & "(and held in the assigned Variant variable is """ & TypeName(ThingReturned) & """) is:" & vbCrLf & "values of " & vbCrLf & strThingsReturned
Else ' Single value is returned
Debug.Print ThingReturned; " "; TypeName(ThingReturned): MsgBox Prompt:="Returned using option Type:=" & Stear & " (" & dicLookupTableMSRD.Item(Stear) & ")" & vbCrLf & "(and held in the assigned Variant is Type Name """ & TypeName(ThingReturned) & """) is:" & vbCrLf & CStr(ThingReturned) ' The CStr appears only to be necessary for the case of an error in ThingReturned in the Message box. Debug.print seems to convert it to a string. All other things are converted to a String
End If
Next Stear
End Sub





http://www.excelfox.com/forum/showthread.php/1828-How-To-React-To-The-Cancel-Button-in-a-VB-(not-Application)-InputBox
https://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post4411660
https://www.excelforum.com/excel-programming-vba-macros/1125597-call-byval-byref-strptr-address-please-run-a-code-for-me-and-post-me-the-results.html
https://www.mrexcel.com/forum/excel-questions/917689-passing-array-class-byval-byref.html#post4412382
http://codevba.com/excel/application_inputbox.htm#.Wmn5adThBhF
http://www.mrexcel.com/forum/excel-questions/447043-left-top-arguments-application-inputbox-method.html
http://www.vbforums.com/showthread.php?617519-RESOLVED-Excel-InputBox-position-works-in-2003-but-not-2007
https://stackoverflow.com/questions/13275692/application-inputbox-left-and-top-not-working-in-excel-vba-why/
https://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-15.html#post4608252
https://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-15.html#post4618975
StrPtr(MyVaraibleNotYetUsed)=0 http://www.excelfox.com/forum/showthread.php/1828-How-To-React-To-The-Cancel-Button-in-a-VB-(not-Application)-InputBox?p=10463#post10463
https://www.mrexcel.com/forum/excel-questions/35206-test-inputbox-cancel-2.html?highlight=strptr#post2845398
https://powerspreadsheets.com/excel-vba-inputbox/
https://www.excelforum.com/tips-and-tutorials/1274147-masked-inputbox-inputbox-for-passwords.html
http://www.eileenslounge.com/viewtopic.php?f=30&t=31495&p=243779#p243776

[DocAElstein]

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

The major 2 differences of interst to us here, between the ( VBA ) InputBox Function and the ( Application ( Excel ) ) InputBox Method were seen to be that
Rem1_a) The InputBox Method has the ability to make a Worksheet range selection whilst the Pop up box is active
_b) The InputBox Method is flaky, aka Bug features
_b)(i) Microsoft “Help” doesn’t work
Rem2_b)(ii) the positional arguments don’t work in Excel 2007+
Some attempt will be made to explain some Windows API User 32 dll Programs background ideas to make a simple Pop Up to come close to realising a Pop Up User InputBox with range selection.

Part a) Worksheet range selection whilst the Pop up box is active, ( and _b)(i) Microsoft “Help” )

Manipulating “Windows”.
It would appear the word “Windows” 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.
Doing this in any language is a task for a computer genius, and in Visual Basic the documentation is very sparse. But “API User 32 dll Programs” would appear to make this possible. I can only attempt to get a working solution with a very light-minded fecile understanding.
It would appear that 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.

API , “API Calls”
The things discussed in the last section gets bundled up in an imprecise intimidating term API, for Application Programming interface. They are usually contained in a Folder with a name similar to User 32.
Another seemingly intimidating phrase is “API call”. You may hear the term “I am using API calls”. It just means usually that you are using those things and related “Windows” concept
Here’s a “API Calls” “thing” that I am getting familiar with using for now.
Function thing __ SetWindowsHooksExample AliAs SetWindowsHookExA
This is one of the Library programs that can be used. I have been told by some professionals that in actual fact these Library programs are organised in a similar way to the Libraries that one can pseudo Import by “checking a reference” in the list of available to VBA code libraries. However by some subtlety that they are not sure about they cannot be used in a code in the way of through declaring ( Dim ing ) them and then after assigning a variable using that variable to “get at” the various Methods / Functions inside them.
In place of the normal declaring ( Dim ing ) that would be within a routine, in the case of the Library programs being used here, you must do a sort of initial globial type Declaration. For this thing that I am intending to use, it looks like

Private Declare Function SetWindowsHookExample Lib "user32" Alias "SetWindowsHookExA" (ByVal Hooktype As Long, ByVal lokprocedureAddress As Long, Optional ByVal hmod As Long, Optional ByVal dwThreadId As Long) As Long

Declareing Declare Type Functions
You don’t always need the AliAs bit in these things. ( It just means _ this Lib "user32" _ Ali As ¬_ “that” _ (that is the Microsoft name , this is any name that I choose to use) ). Occasionally something can only be done to the AliAs where numbers and variable used to refer to things are concerned. It is subtle general point in computing that you might get problems when a number is used to refer to something that might take or give a number at some point. But you might need to do that, so having an intermediate word is a workaround for that so that the number is set to a word which is then related to a word that might be being referred to or returning a number.
Function = Word
Word = 873248
So the Function can be referred to by a number indirectly, --- occasionally this may not be possible directly, --- Function =837547 might error for subtle computer reasons.
It seems to do no harm to use an AliAs when you don’t need it and it helps to make a code prettier.
( Per PM request, Just as a comparison, I did no AliAs for one of the other things that I am using, so you can easily work out the syntax difference ( Function GetCurrentThreadId Lib "kernel32" () As Long ) http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10478#post10478 )
Once Declared you can think of them to a first approximation as a function written in a code module in the Folder on your computer with the name something like “User32” or “User32dll” or similar. You then use them to a very crude approximation as you would any conventional function that you may have made and which is typically in a VBA code module, like pseudo
_ x = SetWindowsHookExample( 3 , y , _..__…… etc )
For use in a normal code you can use Private or Pubic. As in convectional VBA Functions Pubic will not confine the use of the function to the macro module in which it is in.
For a class code module, such as a worksheet code module ( To get there, right click the worksheet's tab and select View Code ), these Declare type functions must be Private

Owned “Windows”, and/ or z order.
It is well above my knowledge to explain all concepts here, and as noted some things will have to be read as “on the tin” or in other words its faecile value.
A Pop up is apparently always the one on top of to be seen ( “above on the screen “z axis” “ , - as a approximation the z axis is in the direction looking at it ) of the Window to which they belong and they always “belong”” to a parent window… well maybe something is not quite clear there…
It is not always clear what “z option” does what, and even professionals sometimes seem to choose it from trial and error .
But anyway these are two things that will need to be taken into the equation… or rather the “API calls” that I do..

Hooking a “Window” to Handle it ..Computer Bollox terminology.
I have needed to get some terms undefined correctly. Words like Handle and Hook are computer terms similar to the word Bollox in normal language and can be used alone or in conjunction with other words to have some meaning possibly in the context in which they are used but cannot have any precise meaning. Defining them as some computer bollox to do with handling and identifying Windows is a useful way to understand these terms.
Some handle bollox will need to be taken into the equation… or rather the “API calls” that I do..
Some published literature even supports my somewhat naive and critical resume, saying the words can mean a number of things. In our case the handle can be thought a number identifying a Window. A Hook can be thought of as hook or trip trap placed in some run or chain of events cause shuddering or jerking off of a procedure. This is related to the idea os Sub Classing of a window discussed below as a possible solution to the second _b)(ii) the positional arguments

MsgBox Pop up.
At the time of writing this I have not figured out or found any InputBox API . I have found and got working some Message Box API’s . So for now I will look at Message Boxes Pop Ups in API calls. To make a better 1 to 1 type equivalent to my attempt and a InputBox method, some InputBox API would have been better, but for the specific requirement of making a range object selection, my attempt and the InputBox method work similarly. The non modelness is an important simularity

Handleing of the MsgBox Pop up
The code in the following post attempts to put some clarity, as far as I am able, on what the handle of the MsgBox, or rather on what the handle of the API User32 Windows dll MessageBoxA Function might be about.
I am able to find A “handle to a Window” that allows me to make the API User32 Windows dll MessageBoxA appear to work as the Standard VBA MsgBox, but with the extra feature of non modalability.
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 more 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

[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/development-testing-forum/1215283-gimmie-da-codexamples-call-in-the-appendix-posts-2018-no-reply-needed-but-if-u.html#post4822413 )



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

_._______________________________


_._______________________________

[DocAElstein]

https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=10471&viewfull=1#post10471


Part _Rem2_b)(ii) the positional arguments

Although, the important feature of the spreadsheet selection possibility whilst the Pop Up is up has been realised, ( and also another issue, the Microsoft “Help” appears to work ) , the position and size is somehow in someway coming to a default value, there is no position or size option
I am guessing that possibly it is not sized as such and that there are predetermined rules for an owned “Window”. There size is possibly predetermined from some pre determined “Window” that does not generally have the resize option , although positioning is somehow possible..

The feature of controlling the size was an option in both the Application InputBox Method and the InputBox Function. In the case of the InputBox Method it appeared to broken from Excel 2007+

I am not particularly interested in sizing and positioning after the event as I want a fairly fundamental Pop up for simple user input.

It turns out that this requires some very complicated processes.
It requires a full understanding of “Window”s. I can only attempt a very short very light-minded fecile overview. That will be discussed in the next posts: in Brief:

Sub Classing / Redefining 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 dll. 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 )

Trigger Events Codes in Windows programs
If you are familiar with Event triggered codes in VBA then the way to do this you can consider as similar idea:
You can arrange that a used “Window” Function is modified as it is used.
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. This will mean that generally this event triggered type code will only work once. A simple solution to make the solution work in a code for any number of message boxes would be use a simple routine which does two things, like pseudo

Sub()
' A) Hook the pseudo Windows Sub Class Function WinSubWinCls_JerkBackOffHooKterd
' B) Call the MessageBoxA
End
***The actual form of this will be close to these pseudo codings. It will be explained a bit more in detail later..

Sub HookAPIssinUserDLL_MsgBoxThenDropIt()
' A) HOOK Hook the pseudo Windows Sub Class Function WinSubWinCls_JerkBackOffHooKerd
Let hHookTrapCrapNumber = SetWindowsHooksExample(5, AddressOf WinSubWinCls_JerkBackOffHooKterd, 0, GetCurrentThreadId) ' (5-pull before flush, somehow arranges that the function gets called ,
' B) Call the MessageBoxA
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 Message Box
End Sub
Or

Sub HookAPIssinUserDLL_MsgBoxThenDropIt()
' a) HOOK Hook the pseudo Windows Sub Class Function WinSubWinCls_JerkBackOffHooKerd
Dim BookMarkClassTeachMeWind As Long: Let BookMarkClassTeachMeWind = 5
Let hHookTrapCrapNumber = SetWindowsHooksExample(BookMarkClassTeachMeWind, AddressOf HoldYaBackCalledYaBackClapTrap, 0, GetCurrentThreadId) ' (5-pull before flush, somehow arranges that the function gets called ,
' b) Call the MessageBoxA
APIssinUserDLL_MsgBox &H0, "Select Range", "MutsNuts AkaApi working ApplicationPromptToRangeInputBox", vbOKOnly ' Pseudo Non Modal
End Sub


' B) Call the MessageBoxA
This is simply our


__ APIssinUserDLL_MsgBox(hWnd:=&H0, Prompt:="Yes, or No to ReCheck, Cancel for help ", Title:="Selection Check: " & RngSel.Address, Buts:=vbYesNoCancel) ' Pseudo Non Modal MsgBox


We need now to find the solutions to two things, I will try to give them a identifying “handle”
HOOK _ ' A) Hook the pseudo Windows Sub Class Function WinSubWinCls_JerkBackOffHooKterd
and then
PROC _ we need the ( VBA in this case ) Function/ Sub Procedure in that we will be hanging, / dangling on, a chain of events / effectively making a Windows Sub Class. I am calling this:
Function WinSubWinCls_JerkBackOffHooKterd.



Sub Classing Windows aka in English: One way to mess about and modify the underlying Windows processes within a VBA code to get extra and novel solutions

The following I will repeat a few times as it is a seemingly complicated way of doing stuff, and a bit off a *** “chicken and egg” thing to try and explain. It is very difficult to start anywhere, as you usually need to go back and forth constantly when explaining the underlying concepts. This is reflected in the very complicated interrelated processes and interactions going on in a final solution
I will do my best to explain it in some sort of logical order. In parallel to the last two issues we have

HOOK_ Setting a hook
The Hook bollox word in this situation can be thought of as
_(i) lots of hooks on a lot of different things / events that might happen. Alternatively a cyber robot keeping his beady eyes on lot of different things / events that might happen

PROC _(ii) Some process / procedure / Function or similar that would be done on if any of the hooks are aroused. This (ii) may sometimes be referred to in literature as the hook procedure





Refs:
Microsoft Windows Features : types, states, size, and position : https://msdn.microsoft.com/en-us/library/windows/desktop/ms632599(v=vs.85).aspx

User 32 API VBA Function list :
Dan Appleman “Visual Basic Programmer’s Guide to the Win32 API”
https://www.excelforum.com/excel-programming-vba-macros/1215891-excel-vba-function-method-api-windows-function-user32-dll-alias-declare-library-list.html


Getting some Messsaga Box API Call’s working:
http://www.eileenslounge.com/viewtopic.php?f=18&t=28885
https://www.excelforum.com/excel-programming-vba-macros/1217445-help-understanding-hook-messageboxa-positioning-with-api-windowshook-user32-dll-stuff.html
http://www.vbforums.com/showthread.php?329373-MsgBoxEx-Extended-Message-Box[/url]
https://eileenslounge.com/viewtopic.php?p=321874#p321874

Microsoft help (argument options): 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

[DocAElstein]

Sub Classing Windows aka in English: One way to mess about and modify the underlying Windows processes within a VBA code to get extra and novel solutions


“First Part HOOK” Setting a hook
The Hook bollox word in this situation can be thought of as
_(i) lots of hooks on a lot of different things / events that might happen. Alternatively a cyber robot keeping his beady eyes on lot of different things / events that might happen
There is an API program available for this is

Private Declare Function SetWindowsHookExample Lib "user32" Alias "SetWindowsHookExA" (ByVal Hooktype As Long, ByVal lokprocedureAddress As Long, Optional ByVal hmod As Long, Optional ByVal dwThreadId As Long) As Long ' https://msdn.microsoft.com/en-us/library/windows/desktop/ms644990(v=vs.85).aspx
Setting a Windows Hook
The way this particular API Function, SetWindowsHookExA , works must have been deliberately designed to confuse in my opinion. I say this in particular because of the returned Long number is probably better considered as one of the functions arguments to be passed in some hidden process or procedure or instructions written on a bookmark or written on the class defining notes of , many bookmarks
Very approximately and simply put: This code line _..
SetWindowsHookExample(5, AddressOf HoldYaBackCalledYaBackClapTrap, 0, GetCurrentThreadId)
_.. is “responsible” or “organises” to a large extent how and when the Function ( PROC hook procedure )which is discussed in the next post is called
This code line might give the impression that there are 5 arguments type parameters to it.
SetWindowsHookExample(5, AddressOf HoldYaBackCalledYaBackClapTrap, 0, GetCurrentThreadId)
To see pseudo all the arguments you must consider that code line in the full used form
hHookTrapCrapNumber = SetWindowsHookExample(5, AddressOf HoldYaBackCalledYaBackClapTrap, 0, GetCurrentThreadId)
Pseudo we have, or rather Declareation makes more sense to me


Declare Function SetWindowsHooksExample Lib "user32" Alias "SetWindowsHookExA" (ByVal hHookTrapCrapNumber As Long, ByVal Hooktype As Long, ByVal lokprocedureAddress As Long, Optional ByVal hmod As Long, Optional ByVal dwThreadId As Long) As Long
, in other words to look in a code like this:
SetWindowsHooksExample(hHookTrapCrapNumber , 5, AddressOf HoldYaBackCalledYaBackClapTrap, 0, GetCurrentThreadId)
It probably should have been. But never mind. But because of this daft way of putting the hHookTrapCrapNumber as the Function’s return, I must have a global variable for my hHookTrapCrapNumber so that I can get later at it in the hook procedure Function WinSubWinCls_JerkBackOffHooKterd

Here an attempt to explain those arguments in English:
(… my HoldYaBackCalledYaBackClapTrap or WinSubWinCls_JerkBackOffHooKterd is my hook Procedure .. just to remind )

hHookTrapCrapNumber : This has the computer bollox word hook in it, so no one quite knows how to define it.. it can be thought of as a number of a Window in this case. But not in classic “seen Window”. In this case it is more of a number to identify a bookmark or class of , many bookmarks. Alternatively it is the name of our cyber Robot who could be called a Microsoft Windows program. He may or may not be residing in the User 32 Folder.

5 : lets call this a Type of a hook a Handled or set trap point , ( or in English think of it as defining/ making a book mark type to be put in a book / books, that is / are likely to be going to be read at some later point in time_ 5 is the one that is defined or set to be fired by most stuff to do with window manipulations, as well as some other more advanced stuff that goes on. Alternatively this could be selecting the class of bookmark for those that will be placed in a few places by the API Function SetWindowsHookExA
The number 5 is what we want. But we have mentioned that for subtle computer reasons it is generally a good idea to have and use a variable holding this number rather than direct number. It is also helpful in our particular case as we will see that another option defining number sent to out hook procedure could be 5 , and we test for that later. So just to avoid confusion I use a variable _ BookMarkClassTeachMeWind = 5

AddressOf : This is called an Operator. An Operator is a computer word which in normal English means “some Bollox or other”. This stores in some secret place a number used to identify where to find the WinSubWinCls_JerkBackOffHooKterd
So you had better have a WinSubWinCls_JerkBackOffHooKterd or you will get a compile error when running a code with the AddressOf WinSubWinCls_JerkBackOffHooKterd inside the (arguments, , , ) section of the use of the dll / User32 Function

0 : Think of this as one of two radio buttons. The other one is the second argument , AddressOf WinSubWinCls_JerkBackOffHooKterd . The arguments are a bit similar. It was probably just done in two rather than one to confuse and intimidate me and my kind. So it is set to 0 as the other one is more like how and where my “hook” Procedure Bollox is

GetCurrentThreadId : The Thread is what is going on, I expect that means in this case my VBA. My computer might do something else with or without me knowing. Most things going on will have a Thread number. When used in my code, Function GetCurrentThreadId will return an identifying number referring to the Excel instance that that code line is in.
_.____

Where are we?
At this point, that is to say on completion of this code line, we can think of many Bookmarks having been placed. Think of an idea of a Windows form which we built a class Form for, and may have many open instances of it. It is difficult to find any better description of the situation. Whatever instances or “things” are hooked in place, they are waiting like a trap waiting to be tripped
There could be a few things that might trigger them off. The purpose of doing all this is to trigger it off when our Message Box Pops up. There is no guarantee that other things going on as a result of out message box coming up may not also trigger one or more of the things. In fact, experiments by me have shown that typically 4 times a bookmark “hook” is triggered before the event that I actually want does the triggering.
We need to try and arrange that we react appropriately to the required trigger.
Some selection is possible and this will be a applied in the PROC hook procedure.
In addition the possibility of a reacting to a false trigger in a program generally is minimised by putting the MessageBoxA call immediately after the call if the API SetWindowsHookExA function, as is the case in our Sub Procedure, Sub HookAPIssinUserDLL_MsgBoxThenDropIt().

Sub HookAPIssinUserDLL_MsgBoxThenDropIt()
' a) HOOK Hook the pseudo Windows Sub Class Function WinSubWinCls_JerkBackOffHooKerd
Dim BookMarkClassTeachMeWind As Long: Let BookMarkClassTeachMeWind = 5
Let hHookTrapCrapNumber = SetWindowsHooksExample(BookMarkClassTeachMeWind, AddressOf WinSubWinCls_JerkBackOffHooKterd , 0, GetCurrentThreadId) ' (5-pull before flush, somehow arranges that the function gets called ,
' b) Call the MessageBoxA
APIssinUserDLL_MsgBox &H0, "Select Range", "MutsNuts AkaApi working ApplicationPromptToRangeInputBox", vbOKOnly ' Pseudo Non Modal
End Sub
So at this point in the discussions we are ready to consider and write our PROC hook procedure such that it will do what we want it to do after this code line is done
_ MsgBox &H0, "Select Range", "MutsNuts AkaApi working ApplicationPromptToRangeInputBox", vbOKOnly ' Pseudo Non Modal

So at this point in our descriptions we have got as far as having and possibly understanding a VBA Sub Procedure, Sub HookAPIssinUserDLL_MsgBoxThenDropIt().

The significance of this is that in a code we will be able to
_ Call HookAPIssinUserDLL_MsgBoxThenDropIt()
This will have the effect of doing the necessary to arrange that then the_..
_ MsgBox &H0, "Select Range", "MutsNuts AkaApi working ApplicationPromptToRangeInputBox", vbOKOnly ' Pseudo Non Modal
_..riggers off the PROC hook procedure, which I am going to call
Function HoldYaBackCalledYaBackClapTrap(!!!argumentssended !!! )
Or rather some triggered happening takes place and somewhere towards the end of this happening my HoldYaBackCalledYaBackClapTrap(!!!argumentssended !!! ) is passed / sended some arguments and set off running

“Triggered happening” of………
Bookmark Procedure or
Hidden Microsoft triggered code or
What is trigger set to do / !!!arguments send.. , , !!! or
Microsoft CBTProc callback function ( https://msdn.microsoft.com/en-us/library/windows/desktop/ms644977(v=vs.85).aspx )
This Procedure should not be confused with my, still to be written, Function HoldYaBackCalledYaBackClapTrap(!!!argumentssended, , !!! ……) or maybe in a way of thinking it is. I am not too sure.
In any case some happening has taken place and the referenced article is related to this Happening. This particular happening rather than any other is done by virtue of us selecting the option of 5 when setting the hook ( BookMarkClassTeachMeWind = 5 --- SetWindowsHooksExample(BookMarkClassTeachMeWind, , __)
or
SetWindowsHooksExample( 5 , , __) )

The important information from the Microsoft article is the information regarding three parameters that I have indicated by !!!arguments send..!!!
Somehow what the CBTProc callback function does is to “hold” these arguments for our Function HoldYaBackCalledYaBackClapTrap(!!!argumentssended, , !!! …… .

So it is going to be important to write a Function in that three parameter argument signature line form. It seems as though that article describes some “skeleton” structure that I must adhere to in the Function design.
It is important also to use a Function rather than a Sub routine: I do not think that I need to concern myself with this returned value. But I need to make my structure support this. I think. ( ..it crashes my Excel if I do a Sub routine ! )


Summary of so far..
This post summarised the main and pseudo Calling codes. At least it does in terms of the API bollox part of it.
I say pseudo Calling because the main purpose of the codes is to “Call” my “ “Hook” Procedure “ Function, the Function to be set up in the next post as the Function HoldYaBackCalledYaBackClapTrap “ “Hook” Procedure “
But the main strange unusual characteristic of the code concepts is that there is no conventional Call code line

The whole point of what is difficult to understand is that the codes have done the stuff necessary so that when the last main bit of the code is done, ( APIssinUserDLL_MsgBox ___ ) the event that then occurs will trigger / fire the Function HoldYaBackCalledYaBackClapTrap.

With hindsight is not difficult to understand and say in plain English:
If _…
_..you just had the last main code bit of this sort of form:_..
APIssinUserDLL_MsgBox &H0, "Select Range", "MutsNuts AkaApi working ApplicationPromptToRangeInputBox", vbOKOnly
_... then a message box pops up. That is all there is to it.
_.. But, … because of all the hooking bollox, when that Message box starts to come up, or when the chain of events waggles in anticipation, then that triggers/ fires the Function HoldYaBackCalledYaBackClapTrap

[DocAElstein]

Sub Classing Windows aka in English: One way to mess about and modify the underlying Windows processes within a VBA code to get extra and novel solutions

“Second Part PROC” my hook procedure


My Function HoldYaBackCalledYaBackClapTrap

( CBTProc callback secret god knows where function ( https://msdn.microsoft.com/en-us/library/windows/desktop/ms644977(v=vs.85).aspx ) )

Summary how did we get here:
It is difficult to separate the discussions into the main code and the Function which is the main Theme of this Post. Probably with hindsight an intermediate post is needed to talk about some hidden secret coding which links the two.. ( CBTProc callback secret god knows where claptrap ) but anyway….
Lets say that at this point in time that the code discussed in the last post has been run to the point that the reasonably normal VBA code line _..
APIssinUserDLL_MsgBox &H0, "Select Range", "MutsNuts AkaApi working ApplicationPromptToRangeInputBox", vbOKOnly
_.. has just been passed / is in the process of being carried out.
( I admit that this code line is actually also an API thingy, but for the purposes of this discussion it can be taken as working very similarly to the normal conventional VBA MsgBox Function – most of its arguments are doing the same )
But before that, a line has previously been done as was discussed in detail in the last post:
hHookTrapCrapNumber = SetWindowsHooksExample(5, AddressOf HoldYaBackCalledYaBackClapTrap, 0, GetCurrentThreadId)

The effect of that is / was a follows:
In some predetermined set of instructions or planned chain of events, a “hook” or “marker” or “clap trap” or “page marker” or “trip trap” was made. This was given an identifying number which was returned by that “API Function” and it was chosen to be placed in the main code in a globial variable hHookTrapCrapNumber. I do not think that this number identifies the “page in the book” where the bookmarker is. I think it just is listed somewhere in a list of any active / set up book marks. I guess there might be / could be a few, so you need to distinguish them. It was also disgust that possibly the number refers to set instances of a Bookmark class: there may be a few , but they are all effectively connected / activated by the number hHookTrapCrapNumber existing in some register.
The bookmarker has a particular type, ( 5 ). The type will be responsible for catching the Message box code line from the last post, ( APIssinUserDLL_MsgBox &H0, "Select Range", "MutsNuts AkaApi working ApplicationPromptToRangeInputBox", vbOKOnly ). That fires my hook procedure Function. Other things may also fire my hook procedure Function. They may or may not be themselves also related to the Popping up of my message Box.

What can easily confuse is that we do not need that number in hHookTrapCrapNumber initially. The number refers to the book mark.
When we have finished this Function code we will need it to take the Bookhook marker out.
There is an API Function too do this:
__UnHookWindowsHookCodEx hHookTrapCrapNumber
That code line will be used in my function, so we need to have had to put it at the start of the code module in a global variable:
Private hHookTrapCrapNumber As Long
The effect of using the option 5 has been to effectively to have another program which we never see “made available “ ( it might be another dll User 32 one or some other one hidden god knows where on the computer). I think a good guess and explanation might be to say that this hidden code “sits” on the bookmark waiting to be run when the chain of events or book that the bookmark is in waggles/ shakes it into life. The hidden coding when it runs is somehow giving the information such that my written hook procedure Function must be written in such a way to recognise. part of what the hidden code does is to pseudo to write on the bookhook mark a number…. which…can be thought of as a number of a Window in this case… possibly… but I am not really convinced… maybe that is the number of a Window being opened.. There probably is another couple of numbers written on the bookmark:
_The Address Of this “rat-trap-back-W_nd-rap-hold-back-call-back-holdhook roll back to my bollox” function HoldYaBackCalledYaBackClapTrap
( Address Of my hook procedure Function HoldYaBackCalledYaBackClapTrap )
and
_ the “address” of the CBTProc callback secret god knows where function
No wonder no one knows what’s going on. I doubt I would if I was not a bit autistic. We never get to see the those number or numbers . There might be a API function code to get those, - but that will probably have to wait 10 years to find when I answer the API List thread. The fact that the function does get called is the proof I guess that the correct one was got by the
SetWindowsHooksExample(5, AddressOf HoldYaBackCalledYaBackClapTrap, 0, GetCurrentThreadId)….
In the last post it was suggested that the Microsoft CBTProc callback Function might be some sort of skeleton signature line place holding thing for the argumentstosend to my hook procedure Function HoldYaBackCalledYaBackClapTrap. Effectively it defines how my signature line must be written such that the data made available ( 3 Long numbers ) are in the predefined order so that I know which is which.

One could say, as a chap Don I know did, “ The function you set up as the hook callback has to match the CBTProc specification (parameter names are not important, but order, type and number are)”
I think in the meantime I understand.
_... The secretly held info of Long number Address Of HoldYaBackCalledYaBackClapTrap was already known. It served its purpose to “get us to” the signature line of the function

Private Function HoldYaBackCalledYaBackClapTrap(ByVal lMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long ' ByVal CopyNumberFroNxtLvl As Long) As Long This bit wont work… The function you set up as the hook callback has to match the CBTProc specification (parameter names are not important, but order, type and number are)……
“So Here” we are. We are Here:
Function HoldYaBackCalledYaBackClapTrap
I suspect now, at this moment in time, at the trigger / trip / stumble over the bookhookmark , we are “sent” to the function with information pertaining to the _...
___( _trip type detected ,
_________ a number…. which…can be thought of as a number of a Window in this case ,
________________ a number god knows what but it ain’t important anyway. )
_.. or maybe the “thing” cursor progression robot walking through the transverse multi plane simultaneous dimensioned computer memory is stumbling never ending as long as the bookhook mark is there, which it is as we have not taken it out yet, … but we will catch this one with our_........
__If lMsg = 5 Then _...........
___( _ The system is about to activate a window. ,
_________ a number…. which… in this case .. my MessageBoxA window ,
________________ maybe some info about a cat and /or mouse )

The cursor progression robot had stumbled about 5 times or so before he tripped up on the one I wanted. My logs showed that. ( https://www.excelforum.com/development-testing-forum/1215283-gimmie-da-codexamples-call-in-the-appendix-posts-2018-no-reply-needed-but-if-u-2.html#post4829796 https://www.excelforum.com/development-testing-forum/1215283-gimmie-da-codexamples-call-in-the-appendix-posts-2018-no-reply-needed-but-if-u-2.html#post4831335 https://www.excelforum.com/development-testing-forum/1215283-gimmie-da-codexamples-call-in-the-appendix-posts-2018-no-reply-needed-but-if-u-2.html#post4831382 )
I couldn’t get any satisfaction in understanding the logic of Functions of this nature which I found on searches. ( And those posts got deleted in some Witch hunt to ban me from that Forum anyway…. )

I decided to start again on the Function from scratch.
If the syntaxly correct arguments are passed, then the Function “starts” as any other.
If I have any normal syntax errors then that does not seem to be picked up when I write the code.
But if there are any then running the main code will cause Excel to crash once the function “starts”
I played around a bit.
The idea of the Function in this application is to position and size the window of a Message box.
But starting from scratch. I just do this-..

Private Function HoldRapeAHookPro(ByVal lMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
End Function ' HoldRapeAHookPro

_.. and I get no error. The main code completes normally with a Message box. Further investigation suggests that the Function is done many times, but it proved difficult to measure how many- an attempt with variations of_...
Let GlobinalCntChopsLog = GlobinalCntChopsLog + 1 : Debug.Print GlobinalCntChopsLog
_... a very high number was suggested but the Immediate window seemed to go on printing more and more lines after completion of the code when anything was clicked on. Simply removing the Debug.Print and looking at the final value of GlobinalCntChopsLog yields a much smaller number. Possibly some strange mismatch in execution times and states. The Immediate window appears to effect the code.


Various Published codes doing something similar API things, _...

http://www.vbforums.com/showthread.php?617519-RESOLVED-Excel-InputBox-position-works-in-2003-but-not-2007
https://www.techrepublic.com/blog/10-things/10-plus-of-my-favorite-windows-api-functions-to-use-in-office-applications/
http://www.eileenslounge.com/viewtopic.php?f=18&t=28885#p223629
https://stackoverflow.com/questions/13275692/application-inputbox-left-and-top-not-working-in-excel-vba-why/
http://www.mrexcel.com/forum/excel-questions/447043-left-top-arguments-application-inputbox-method.html
http://www.excely.com/excel-vba/define-a-position-of-messagebox.shtml
https://www.excelforum.com/excel-programming-vba-macros/1217445-help-understanding-hook-messageboxa-positioning-with-api-windowshook-user32-dll-stuff.html
_... seemed to go into a strange recursion which seemed to have a stack limit of 30. Once again the details and Logs of those at the previous links have been deleted.

I started to start from scratch having gained a small level of understanding in the process of preparing this and the referenced deleted Threads.

So I start again in the next Post.

[DocAElstein]

Some conclusions from experiments on various codes types.

Two main ‘_- observations

‘_- Stopping the function.
I have done a number of experiments, as briefly explained at the end of the last post.
Some important observations:
It seems that my “hook procedure” function will potentially be triggered indefinitely. Possibly this is reasonable. It is further reasonable that the simple API Function call already discussed is needed to stop the function being triggered once my function has done its main purpose:
Here an Ali -As-‘d version of this code line:
UnHookWindowsHookCodEx hHookTrapCrapNumber
The corresponding Declare line for that would be
Private Declare Function UnHookWindowsHookCodEx Lib "user32" Alias "UnhookWindowsHookEx" (ByVal hHookTrapCrapNumber As Long) As Long


‘_- Doing the Pop up window sizing and positioning ( API User32 dll program SetWindowPos)
Originally the issue for which my Hook procedure Function was required was the _ Part _b)(ii) the positional arguments _ .
The API Function which appears to have been used in Visual Basic and VBA codes in similar issues to this is the
SetWindowPos ( https://msdn.microsoft.com/en-us/library/windows/desktop/ms633545(v=vs.85).aspx ). This looks potentially very useful as it can change_..
_..all three positional co ordinates, horizontal (x), vertical (y), and the “z axis” “Z order”. ( The last z coordinate can be thought of to a first approximation as the axis looking onto the screen, the “top z” then being the window “seen” above all others ).
_.. also, as a bonus, it can also be used to change the seen Window width and height.
The syntax in a code line use of this function is pseudo like
_ SetWindowPosition _ WindowIdentifyinghandle, zorder , x , y , width , height , zFurtherInfo
The two z__ options are not too intuitive, and even some computer Professionals make an inspired guess for them based on a partial understanding of the documentation. The rest of the argument parameters are as “written on the can” _ x , y , width , height ( “says the tin”.. https://www.excelforum.com/excel-programming-vba-macros/1217445-help-understanding-hook-messageboxa-positioning-with-api-windowshook-user32-dll-stuff.html#post4831198 )

From the experiments with use of this I was concerned about a seemingly unnecessary recursion process that seemed to set in. I also observed this in published Functions which were intended to do something similar.

My final Hook procedure Function differs therefore slightly in any I have seen so far in that it a globinal variable, GlobinalCntChopsLog, is used to keep track of the times that my Hook procedure Function is run. The constant jerking back and forth of the function, repeating, presumably by the “Bookmarks” reacting seemingly indefinitely is actually needed. As noted in the previous post we will do some selection to hopefully pick out the correct “event”, that is to say the appropriate event in the chain of events in which the “popping up” of our message box is included. But I am not sure if the extra recursion runs of copies of my function are necessary or desired. - The required action appears to be done on the first use of the SetWindowPosition . However a usage seems itself to trigger the function. I am assuming this is a recursion process so that another copy of my function is made and that is run. It does not, however, go into stack overflow. Consistently at the 29th recursion, it would appear that the recursion stops and each copy function is ended one after the other. ( A guess is that the stack might be limited somehow to 30 ). For my codes this resulted in a total of 6+29=35 runs: My function jerked itself off 5 times until a condition I set to hopefully find the event I was wanting to be caught was satisfied. So on the 6th run the SetWindowPosition code line was done. Experimenting on that showed that at this point all was done and OK as regards the positioning. A further 29 copy runs of the function appeared to be done, suggesting that the SetWindowPosition code line was responsible somehow for this. ( Example of this test code with output Log is shown here: http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10478#post10478 )

Logic of my final Hook procedure Function
At the outset of the function, my gobinial variable is increased by 1. This intended to keep count of the copy number of the Function being run. It will therefore be reduced by 1 at the end of the function. Therefore, should a recursion run take place, ( that is to say a copy of the function is made and run ) , then the gobinial variable will hold the value of 2. A test for that will be made. This is tested for and If a value of 2 is found Then the “hook is released” and the function ended.
When my function is no in a recursion run, that is to say GlobinalCntChopsLog is not equal to 2 then the code proceeds as follows:
The value of the first argument lMsg at the incoming signature line _..
_ Function HoldYaBackCalledYaBackClapTrapRuc(ByVal lMsg As Long, ByVal wParam As Long, ByVal lParam As Long ).
_.. is investigated
This is done for the following reason:
As explained in the lost few posts, one result of the “hook” being “hanged” is that when one of our “bookmarks” is “triggered”, then some coding ( possibly the Microsoft CBTProc callback function ( https://msdn.microsoft.com/en-us/library/windows/desktop/ms644990(v=vs.85).aspx ) ) is responsible for passing information into the three arguments of my Hook procedure Function. ( Possibly this coding itself is responsible for all the other “placing of bookmarks” etc.. previously discussed ).
The first argument defines the type of “event” that has done the triggering. ( Remember we mentioned previously that it is an unfortunate co incidence that the number of 5 is what we are looking for here.. This was also the number which we needed to stipulate in the setting of the hook code line, hHookTrapCrapNumber = SetWindowsHooksExample(5, AddressOf , …….. ). That had a different meaning so should not be confused with the current 5 under discussion. )
The value of 5 specifies that the system is about to activate a window.

So If lMsg = 5 Then the code line_..
_ SetWindowPosition(wParam, 0, 10, 50, 400, 150, 40)
_.. is carried out.
_ wParam ________ is the passed windows identifying number for my Message box
10, 50, 400, 150 ___ are the horizontal and vertical size and positions
_ , 0 , _ , _ , _ , 40 __ The two numbers 0 and 40 are chosen after a bit of intuitive guessing based on the previous given Microsoft references. The end effect is to have the window seen as dominantly as wanted. They are likely to be based to some extent on experimenting in a particular requirement.
( As a function, the SetWindowPos is designed to return a value. In this usage I have not experienced problems using it as a Sub routine Call _..
_ Call SetWindowPosition( , , , , , , )
_.. but to be on the safe side I have used it as a Function returning its return in a Boolean variable, Booloks )
_.__________________

All the ground has been covered and explained as far as I am able to give finally a Pop Up User pseudo InputBox with range selection alternative with API User 32 dll Programs.
The next post will give a Summarised working full codes solution.

[DocAElstein]

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.



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.


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://eileenslounge.com/viewtopic.php?f=27&t=35521&p=276185#p276185 (https://eileenslounge.com/viewtopic.php?f=27&t=35521&p=276185#p276185)
https://eileenslounge.com/viewtopic.php?p=276185#p276185 (https://eileenslounge.com/viewtopic.php?p=276185#p276185)
https://eileenslounge.com/viewtopic.php?p=276185#p276185 (https://eileenslounge.com/viewtopic.php?p=276185#p276185)
https://eileenslounge.com/viewtopic.php?p=276673#p276673 (https://eileenslounge.com/viewtopic.php?p=276673#p276673)
https://eileenslounge.com/viewtopic.php?p=276751#p276751 (https://eileenslounge.com/viewtopic.php?p=276751#p276751)
https://eileenslounge.com/viewtopic.php?p=276754#p276754 (https://eileenslounge.com/viewtopic.php?p=276754#p276754)
https://eileenslounge.com/viewtopic.php?f=30&t=35100&p=274367#p274367 (https://eileenslounge.com/viewtopic.php?f=30&t=35100&p=274367#p274367)
https://eileenslounge.com/viewtopic.php?p=274368#p274368 (https://eileenslounge.com/viewtopic.php?p=274368#p274368)
https://eileenslounge.com/viewtopic.php?p=274370#p274370 (https://eileenslounge.com/viewtopic.php?p=274370#p274370)
https://eileenslounge.com/viewtopic.php?p=274578#p274578 (https://eileenslounge.com/viewtopic.php?p=274578#p274578)
https://eileenslounge.com/viewtopic.php?p=274577#p274577 (https://eileenslounge.com/viewtopic.php?p=274577#p274577)
https://eileenslounge.com/viewtopic.php?p=274474#p274474 (https://eileenslounge.com/viewtopic.php?p=274474#p274474)
https://eileenslounge.com/viewtopic.php?p=274579#p274579 (https://eileenslounge.com/viewtopic.php?p=274579#p274579)
https://www.excelfox.com/forum/showthread.php/261-Scrolling-Marquee-text-on-Userform?p=864&viewfull=1#post864 (https://www.excelfox.com/forum/showthread.php/261-Scrolling-Marquee-text-on-Userform?p=864&viewfull=1#post864)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

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.
Private Declare Function SetWindowsHooksExample Lib "user32" Alias "SetWindowsHookExA" (ByVal Hooktype As Long, ByVal lokprocedureAddress As Long, Optional ByVal hmod As Long, Optional ByVal DaFredId As Long) As Long ' The effect of this will be: In some predetermined set of instructions or planned chain of events, a “hook” or “marker” or “clap trap” or “page marker” or “trip trap” was made. This was given an identifying number which was returned by that “API Function” and it was chosen to be placed in the main code in a globial variable hHookTrapCrapNumber. I do not think that this number identifies the “page in the book” where the bookmarker is. I think it just is listed somewhere in a list of any active / set up book marks. I guess there might be / could be a few, so you need to distinguish them.
Private hHookTrapCrapNumber As Long ' "BookmarkClassNumber --- This makes pseudo Declare Sub() SetWindowsHooksExample Lib "user32" AliAs "SetWindowsHookExA" (ByVal hHookTrapCrapNumber As Long, ByVal Hooktype As Long, ByVal MyloksPROCedureFukAddress As Long, Optional ByVal RadioButton2Out As Long, Optional ByVal duhFredId As Long) As Long It was also disgust that possibly the number refers to set instances of a Bookmark class: there may be a few , but they are all effectively connected / activated by the number hHookTrapCrapNumber existing in some register. The bookmarker has a particular type, ( 5 ). The type will be responsible for catching the Message box code line to call the MessageBoxA, (like APIssinUserDLL_MsgBox &H0, "Select Range", "working ApplicationPromptToRangeInputBox", vbYesNoCancel ). That fires my hook PROCedure Function, WinSubWinCls_JerkBackOffHooKterd. Other things may also fire my hook PROCedure Function. They may or may not be also related to the Popping up of my Box.
' Dim BookMarkClassTeachMeWind As Long: Let BookMarkClassTeachMeWind = 5 ' 5 is Hooktype that I will be using. Using a variable for two reasons: '1_- general point in computing that you might get problems when a number is used to refer to something that might take or give a number at some point. But you might need to do that, so having an intermediate word is a workaround for that so that the number is set to a word which is then related to a word that might be being referred to or returning a number. Function = Word --- Word = 873248 '2_- Just to avoid confusion Later as in this particular case later another option number happens to be 5
Private Declare Function GetDaFredId Lib "kernel32" Alias "GetCurrentThreadId" () As Long ' The Thread is what is going on, I expect that means in this case my VBA. My computer might do something else with or without me knowing. Most things going on will have a Thread number. When used in my code, Function GetDaFredId will return an identifying number referring to the Excel instance that that code line is in. It is actually needed in the setting of the Windows hook code line only ( that which is last argument in SetWindowsHookEx( , , , DaFredId As Long) ... set a hook, confined to the current thread (so it doesn't get triggered by other things going on) and give it the address of the function that you want to call in response to the hook being triggered. In this I will use 5 CBT hook which is triggered generally by Window messages (activating, creating, destroying, minimizing, maximizing, moving, or sizing a window)
' This below takes it off the chain. Or wipe the chain clean. Or remove it from something. Or cancel it. Or Kill it. Or whatever. In any case it needs the identifying number of the "hook", then a simple code line as shown in comment below will do this "Killing" Without doing this the thing seems to go on indefinitely (with or without any recursion. (A recursion is another issue which seems to happen as an additional issue - that occurs when the final API code below (over next line) does its job - that seems to fire my Hook PROCedure function WinSubWinCls_JerkBackOffHooKterd
Private Declare Function UnHookWindowsHookCodEx Lib "user32" Alias "UnhookWindowsHookEx" (ByVal hHookTrapCrapNumber As Long) As Long ' 'Release the Hook This is used in code in a simple code line like:- Call UnHookWindowsHookCodEx(hHookTrapCrapNumber)
'_- === All of the above in section Rem 2 is required so that I am able to organise that when I use APIssinUserDLL_MsgBox another program (my windows hookProcedure program WinSubWinCls_JerkBackOffHooKterd) is triggered. (It has a habit of being triggered indefinitely so the API program Decared in the last line above will be used to stop that happening).
'2(d)=== The Final API program below we need to actually do what we want. (WindowIdentifyinghandle, zorder , x , y , width , height , zFurtherInfo ) '_- Most is obvious, except the z stuff - WindowIdentifyinghandle/wParam is one the parameters passed in some secret process to my Function WinSubWinCls_JerkBackOffHooKterd( , wParam , ) and will be the windows identifying number for my Message box that is popping up. ( ,10 ,50 ,400 ,150 , ) These four numbers are the horizontal and vertical size and positions. ( 0, , , , ,40 ) The two numbers 0 and 40 are chosen after a bit of intuitive guessing based on Microsoft references like https://msdn.microsoft.com/en-us/library/windows/desktop/ms633545(v=vs.85).aspx The end effect is to have the window seen as dominantly as wanted. They are likely to be based to some extent on experimenting in a particular requirement.
Private Declare Function SetWindowPosition Lib "user32" Alias "SetWindowPos" (ByVal hWnd As Long, ByVal zNumber As Long, ByVal CoedX As Long, ByVal CoedY As Long, ByVal xPiXel As Long, ByVal yPiYel As Long, ByVal wFlags As Long) As Long ' This API prog will be called in my hook PROCedure function. So.. Rem 2a)-c) sets "Bookmark"/ series of "Bookmarks"/ Microsoft Windows cyber Robot monitering events (of "type 5", i.e. my Pop up coming up is one such. When such a event occurs my function is triggered by Windows software monitering Robot, he knows where/which my function WinSubWinCls_JerkBackOffHooKterd is from the AddressOf in a "hook setting code line" like (5 ,AddressOf WinSubWinCls_JerkBackOffHooKterd , 0, ThreadID) The monitering Robot program thing passes somehow (a number from a list of event types to tell me more precisely what event it noticed, wParam-identifying number of the Window doing that event, possibly some other mouse thing info thing am not bothered about)
Dim Booloks As Boolean ' I use this in the code line Booloks = SetWindowPosition(WindowIdentifyinghandle, zorder,x, y, width, height ,zFurtherInfo) I don't seem to need this, but as a function, the SetWindowPos is designed to return a value. In this usage I have not experienced problems using it as a Sub routine Call like Call SetWindowPosition( , , , , , , ) but to be on the safe side I have used it as a Function returning its return in a Boolean variable, Booloks
Dim GlobinalCntChopsLog As Long ' I use this to keep track of the copy number of my Hook PROCedure function WinSubWinCls_JerkBackOffHooKterd, that is to say check for when that = 2. If that is the case I do the "unhooking" and Exit the Function
' ========================

[DocAElstein]

Page 2 https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function/page2
Original post #11, #post10483 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=10483&viewfull=1#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.



' ========================
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_MsgBoxThenBringThat MsgBoxUp(ByVal RcelsToYou), then you wont have one when you get back neither, as in HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp 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_MsgBoxThenBringThat MsgBoxUp(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]

New post #12, 31 Oct 2024, (was copied from Original post #11, when the original posts 11 12 1nd 13 were copied on 31 Oct 2024 so it got edited to change it to have contents of original post # 12),
#post24886 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24886&viewfull=1#post24886)





Section 3b)-3c) does two main things.
_ A “Hook” is “Hanged” to both
__ “catch” events similar to my “Non Modal message box” popping up,
and then when it does it
__ triggers off a Function WinSubWinCls_JerkBackOffHooKterd
I have tried to explain everything in more detail in the ‘Comments
What actually appears to happen in end effect is that typically as my message box Pops up the function is triggered 6 times before it gets to the one I actually want which is the Message box window being activated



Private Sub HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp(ByRef RcelsToYou) ' This will by referral To You, (RSel), the actual Pointer of you the original RSel. This is not too important a point here, but intersting if you consider the next line alternative to this one.....
' Public Sub HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp(ByVal RcelsToYou) The RSel Pointer aint Gone anywhere if you do this. Just a copy of the Pointer is here. This will allow you to change the value as the Pointer or a copy of it will tell you where to go and do that... But in neither this line or the last line case have you sent the range object. If you use this line then you will find that the address of the range object will not change, as that refers to the range object of the copy variable in this subroutine. But that will not change the range object of RSel
Set RcelsToYou = Selection ' 3c(-i) Pointer GoneTo -1 WTF
Noughty: ' 3c(0i) Pointer GoneTo 0y WTF
' 3b) Hang A Hook to catch things like APIssinUserDLL_MsgBox, .... HOOK: Hook the pseudo Windows Sub Class Function WinSubWinCls_JerkBackOffHooKerd
Dim BookMarkClassTeachMeWind As Long: Let BookMarkClassTeachMeWind = 5 ' I do not need this. 5 is Hooktype that I will be using. Using a variable for two reasons: '1_- general point in computing that you might get problems when a number is used to refer to something that might take or give a number at some point. But you might need to do that, so having an intermediate word is a workaround for that so that the number is set to a word which is then related to a word that might be being referred to or returning a number. Function = Word --- Word = 873248 '2_- Just to avoid confusion later as in this particular case later another option number in Rem 4 happens to be 5. That is checking for a Window opening. So it is similar to the 5 of BookMarkClassTeachMeWind, but it is a narrowed down version of those window happening things. So a bit of aa coincidence really. Using the variable just reminds me of that.
Let hHookTrapCrapNumber = SetWindowsHooksExample(BookMarkClassTeachMeWind, AddressOf WinSubWinCls_JerkBackOffHooKerd, 0, GetDaFredId) ' (5-pull before flush, somehow arranges that the function gets called ,
' 3c) Bring APIssinUserDLL_MsgBox up
Dim Valyou As Variant: Let Valyou = RcelsToYou.Value: If IsArray(Valyou) Then Valyou = Valyou(1, 1) 'For display Value of Top Left of Selection
Dim Rpnce As Long ' Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in. '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. ) https://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop-4.html
Let Rpnce = APIssinUserDLL_MsgBox(hWnd:=&H0, Prompt:="Yes, or No to ReCheck, Cancel for help ", Title:="Selection Check: Address is " & RcelsToYou.Address & " Value is """ & Valyou & """", Buts:=vbYesNoCancel) ' ' Pseudo Non Modal MsgBox
Set RcelsToYou = Selection: Let Valyou = RcelsToYou.Value: If IsArray(Valyou) Then Valyou = Valyou(1, 1) 'The code waited until you made one of the three message box options. But in this time you could change the selection object
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 --- you should get this HelpGetUpBollox.JPG imgur.com/KdKOYWr
If Rpnce = 7 Then GoTo Noughty ' Option to update the displayed Address and Value in Top Left cell of that range
End Sub
'_-=Rem 4===========..

[DocAElstein]

New Post 13 When I copied all of full page 2, making a new full page 3, this post appeared and it is #post24893 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24893&viewfull=1#post24893)
It came from ..... Page 2 https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function/page2
Original post #11, #post10483 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=10483&viewfull=1#post10483)








Section Rem 4 is the Function WinSubWinCls_JerkBackOffHooKterd
This picks out the specific event I want, my “Non modal message box” being activated, and changes the window dimensions, using the API thing SetWindowPos
( You will need to experiment about with, mainly, the 10 50 400 150 The other two numbers 0 40 you may want to adjust as well. Understanding those two numbers fully is a bit beyond me – it took me best part of a week to figure out WTF all the other stuff was about ).
The last thing this function does is “kill” or “drop” or ““take off” the “Hook”” or ““Unhook” the hook”. If you don’t do that the function seems to be triggered indefinitely.
A last thing on a similar point: Some other codes doing something similar that I saw, seemed to cause a wild recursion: The size adjustment done in the function seemed to set off the function code again. The stack seemed to be limited to 30. I could not see any reason to do that, and in fact it seemed to cause some weird inconsistent ghostly images to appear on my desktop. My function just does the thing that I think it should do once. It appears more stable. I did have a lot of fun braking things before I got the API stuff correct. But since then the code seems to work well without problems in a number of code situations on different computers and different Excel versions.
( In all the situations that I have tried, my code has Exited as I have expected after an If clause detects if the function is on the start of a first recursion run. ( I think it would probably do no harm to unhook directly after the SetWindowPos , just in case the SetWindowPos does not cause recursion. ( It seems to do no harm to unhook a few times) )



'_-=Rem 4============= Some hidden function / bookmark / bookmarks / cyber Robot thing was brought into life ("I hung or set a hook"). That monitors events like my message box popping up. When it catches one it starts this finction and passes to it three parameters. The first tells me with a number more excactly what even took place, the next is the window identifying number of that window doing that particular event , the last parameter is something maybe to do with the mouse god knows what exactly probably even Sid don't know... but looking at his Avatar I probably wouldn't say that to him as he I don't know if I would want to mess with him...
Private Function WinSubWinCls_JerkBackOffHooKerd(ByVal lMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long ' I "set a hook" which should trigger ( things similar to my Meassage box popping up , and gave it the AddressOf this function , 0 , and limited it to this "Thread" on my computer that is to say my Excel )
Let GlobinalCntChopsLog = GlobinalCntChopsLog + 1 ' The idea of this is that I add 1 on entering and subtract 1 when leaving the function. So this would be two if I started an other copy of this code before the first had finished. I am expecting that as the SetWindowPosition seemes to trigger it off again.
If GlobinalCntChopsLog = 2 Then Let GlobinalCntChopsLog = GlobinalCntChopsLog - 1: UnHookWindowsHookCodEx hHookTrapCrapNumber: Exit Function ' If I have 2 then that is an indication that recurtion has taken place, that is to say I started another function run caused by SetWindowPosition triggering it off. So i assume then that SetWindowPosition has done what it should so I can "take the hook off" (as if i did not then the function seems to get triggeredt indefinitely even without recusion), and then I exit the function. So I do expect a second copy of the code to run, but due to this it does not do anything other than take the "hook off". I also reduce the count by 1. It is then at 1. But then the first copy of the function ends from just under SetWindowPosition. So then the count is reduced again and is at the initial 0
If lMsg = 5 Then Let Booloks = SetWindowPosition(wParam, 0, 10, 50, 400, 150, 40) ' 5 here is the number for a window about to be activated. This is probably the one I want. If I catch it when it is starting , 3, then It might then re set the size and position stuff again to the standard after I have done it
Let GlobinalCntChopsLog = GlobinalCntChopsLog - 1 ' Every first copy run of the code has the count reduced to 0 so that when it starts again (as the only first copy active) it will be increased to 1 again to indicate it is a run of the function copy 1
End Function







_.________________________________________________ __________________________________________________ _________________________________-








Edit Dec 2018 : check this out http://www.eileenslounge.com/viewtopic.php?f=30&t=31495 If / when I understand it I will post a follow up about this..

[DocAElstein]

New Post 14 When I copied full page 2 to a full page 3, it appeared and it is #post24894 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24894&viewfull=1#post24894)
It came from New post #12, 31 Oct 2024, (was copied from Original post #11, when the original posts 11 12 1nd 13 were copied on 31 Oct 2024 so it got edited to change it to have contents of original post # 12),
#post24886 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24886&viewfull=1#post24886)



Code 'comment breakdown

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.

Here we go then

The Top Declare/Dim section
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 https://eileenslounge.com/viewtopic.php?p=321874#p321874

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.

Private Declare Function SetWindowsHooksExample Lib "user32" Alias "SetWindowsHookExA" (ByVal Hooktype As Long, ByVal lokprocedureAddress As Long, Optional ByVal hmod As Long, Optional ByVal DaFredId As Long) As Long
' The effect of this will be: In some predetermined set of instructions or planned chain of events
, a “hook” or “marker” or “clap trap” or “page marker” or “trip trap” was made. This was given an identifying number which was returned by
that “API Function” and it was chosen to be placed in the main code in a globial variable hHookTrapCrapNumber.
I do not think that this number identifies the “page in the book” where the bookmarker is.
I think it just is listed somewhere in a list of any active / set up book marks. I guess there might be / could be a few, so you need to distinguish them.
Private hHookTrapCrapNumber As Long ' "BookmarkClassNumber --- This makes pseudo
Declare Sub() SetWindowsHooksExample Lib "user32" AliAs "SetWindowsHookExA" (ByVal hHookTrapCrapNumber As Long, ByVal Hooktype As Long, ByVal MyloksPROCedureFukAddress As Long, Optional ByVal RadioButton2Out As Long, Optional ByVal duhFredId As Long) As Long
It was also disgussed that possibly the number refers to set instances of a Bookmark class: there may be a few ,
but they are all effectively connected / activated by the number hHookTrapCrapNumber existing in some register.
The bookmarker has a particular type, ( 5 ). The type will be responsible for catching the Message box code line to call the MessageBoxA,
(like APIssinUserDLL_MsgBox &H0, "Select Range", "working ApplicationPromptToRangeInputBox", vbYesNoCancel ).
That fires my hook PROCedure Function, WinSubWinCls_JerkBackOffHooKterd.
Other things may also fire my hook PROCedure Function. They may or may not be also related to the Popping up of my Box.
' Dim BookMarkClassTeachMeWind As Long: Let BookMarkClassTeachMeWind = 5 ' 5 is Hooktype that I will be using. Using a variable for two reasons: '1_- general point in computing that you might get problems when a number is used to refer to something that might take or give a number at some point. But you might need to do that, so having an intermediate word is a workaround for that so that the number is set to a word which is then related to a word that might be being referred to or returning a number. Function = Word --- Word = 873248 '2_- Just to avoid confusion Later as in this particular case later another option number happens to be 5
Private Declare Function GetDaFredId Lib "kernel32" Alias "GetCurrentThreadId" () As Long ' The Thread is what is going on,
I expect that means in this case my VBA. My computer might do something else with or without me knowing. Most things going on will have a Thread number.
When used in my code, Function GetDaFredId will return an identifying number referring to the Excel instance that that code line is in.
It is actually needed in the setting of the Windows hook code line only ( that which is last argument in SetWindowsHookEx( , , , DaFredId As Long) ...
..set a hook, confined to the current thread (so it doesn't get triggered by other things going on) and give it the address of the function that you want to call in response to the hook being triggered. In this I will use 5 CBT hook which is triggered generally by Window messages (activating, creating, destroying, minimizing, maximizing, moving, or sizing a window)

' This below takes it off the chain. Or wipe the chain clean. Or remove it from something. Or cancel it. Or Kill it. Or whatever.
In any case it needs the identifying number of the "hook", then a simple code line as shown in comment below will do this "Killing"
Without doing this the thing seems to go on indefinitely (with or without any recursion. (A recursion is another issue which seems to happen as an additional issue - that occurs when the final API code below (over next line) does its job - that seems to fire my Hook PROCedure function WinSubWinCls_JerkBackOffHooKterd
Private Declare Function UnHookWindowsHookCodEx Lib "user32" Alias "UnhookWindowsHookEx" (ByVal hHookTrapCrapNumber As Long) As Long ' 'Release the Hook This is used in code in a simple code line like:- Call UnHookWindowsHookCodEx(hHookTrapCrapNumber)

'_- === All of the above in section Rem 2 is required so that I am able to organise that when I use APIssinUserDLL_MsgBox another program (my windows hookProcedure program WinSubWinCls_JerkBackOffHooKterd) is triggered. (It has a habit of being triggered indefinitely so the API program Decared in the last line above will be used to stop that happening)

'2(d)=== The Final API program below we need to actually do what we want. (WindowIdentifyinghandle, zorder , x , y , width , height , zFurtherInfo ) '_- Most is obvious, except the z stuff - WindowIdentifyinghandle/wParam is one the parameters passed in some secret process to my Function WinSubWinCls_JerkBackOffHooKterd( , wParam , ) and will be the windows identifying number for my Message box that is popping up. ( ,10 ,50 ,400 ,150 , ) These four numbers are the horizontal and vertical size and positions. ( 0, , , , ,40 ) The two numbers 0 and 40 are chosen after a bit of intuitive guessing based on Microsoft references like https://msdn.microsoft.com/en-us/library/windows/desktop/ms633545(v=vs.85).aspx The end effect is to have the window seen as dominantly as wanted. They are likely to be based to some extent on experimenting in a particular requirement.

Private Declare Function SetWindowPosition Lib "user32" Alias "SetWindowPos" (ByVal hWnd As Long, ByVal zNumber As Long, ByVal CoedX As Long, ByVal CoedY As Long, ByVal xPiXel As Long, ByVal yPiYel As Long, ByVal wFlags As Long) As Long
' This API prog will be called in my hook PROCedure function.
So.. Rem 2a)-c) sets "Bookmark"/ series of "Bookmarks"/ Microsoft Windows cyber Robot monitering events (of "type 5", i.e. my Pop up coming up is one such.
When such a event occurs my function is triggered by Windows software monitering Robot, he knows where/which my function WinSubWinCls_JerkBackOffHooKterd is from the AddressOf in a "hook setting code line" like
(5 ,AddressOf WinSubWinCls_JerkBackOffHooKterd , 0, ThreadID)
The monitering Robot program thing passes somehow (a number from a list of event types to tell me more precisely what event it noticed, wParam-identifying number of the Window doing that event, possibly some other mouse thing info thing am not bothered about)


Dim Booloks As Boolean ' I use this in the code line Booloks = SetWindowPosition(WindowIdentifyinghandle, zorder,x, y, width, height ,zFurtherInfo)
I don't seem to need this, but as a function, the SetWindowPos is designed to return a value. In this usage I have not experienced problems using it as a Sub routine Call like Call SetWindowPosition( , , , , , , ) but to be on the safe side I have used it as a Function returning its return in a Boolean variable, Booloks
Dim GlobinalCntChopsLog As Long ' I use this to keep track of the copy number of my Hook PROCedure function WinSubWinCls_JerkBackOffHooKterd, that is to say check for when that = 2. If that is the case I do the "unhooking" and Exit the Function
' ========================

[DocAElstein]

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 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=10484&viewfull=1#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 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=10485&viewfull=1#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


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_MsgBoxThenBringThat MsgBoxUp(ByVal RcelsToYou), then you wont have one when you get back neither, as in HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp 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_MsgBoxThenBringThat MsgBoxUp(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]

This is now post 16, after I copied all of full page 2 zto make a new full page 3
It was New post #14, 31 Oct 2024, it came from the original post #12 when the original posts 11 12 and 13 where copied on 31 Oct 2024
#post24887 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24887&viewfull=1#post24887)




Section 3b)-3c) does two main things.
_ A “Hook” is “Hanged” to both
__ “catch” events similar to my “Non Modal message box” popping up,
and then when it does it
__ triggers off a Function WinSubWinCls_JerkBackOffHooKterd
I have tried to explain everything in more detail in the ‘Comments
What actually appears to happen in end effect is that typically as my message box Pops up the function is triggered 6 times before it gets to the one I actually want which is the Message box window being activated

Private Sub HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp(ByRef RcelsToYou) ' This will by referral To You, (RSel), the actual Pointer of you the original RSel. This is not too important a point here, but intersting if you consider the next line alternative to this one.....
' Private Sub HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp(ByVal RcelsToYou) ' The RSel Pointer aint Gone anywhere if you do this. Just a copy of the Pointer is here. This will allow you to change the value as the Pointer or a copy of it will tell you where to go and do that... But in neither this line or the last line case have you sent the range object. If you use this line then you will find that the address of the range object will not change, as that refers to the range object of the copy variable in this subroutine. But that will not change the range object of RSel
Set RcelsToYou = Selection ' 3c(-i) Pointer GoneTo -1 WTF
Noughty: ' 3c(0i) Pointer GoneTo 0y WTF
' 3b) Hang A Hook to catch things like APIssinUserDLL_MsgBox, .... HOOK: Hook the pseudo Windows Sub Class Function WinSubWinCls_JerkBackOffHooKerd
Dim BookMarkClassTeachMeWind As Long: Let BookMarkClassTeachMeWind = 5 ' I do not need this. 5 is Hooktype that I will be using.
' I am Using a variable for two reasons:
'1_- general point in computing that you might get problems when a number is used to refer to something that might take or give a number at some point. But you might need to do that, so having an intermediate word is a workaround for that so that the number is set to a word which is then related to a word that might be being referred to or returning a number. Function = Word --- Word = 873248
'2_- Just to avoid confusion later as in this particular case later another option number in Rem 4 happens to be 5.
'That is checking for a Window opening. So it is similar to the 5 of BookMarkClassTeachMeWind,
'but it is a narrowed down version of those window happening things. So a bit of a coincidence really. Using the variable just reminds me of that.
Let hHookTrapCrapNumber = SetWindowsHooksExample(BookMarkClassTeachMeWind, AddressOf WinSubWinCls_JerkBackOffHooKerd, 0, GetDaFredId)
' 3c) Bring APIssinUserDLL_MsgBox up
Dim Valyou As Variant: Let Valyou = RcelsToYou.Value: If IsArray(Valyou) Then Valyou = Valyou(1, 1) 'For display Value of Top Left of Selection
Dim Rpnce As Long ' Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in. '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. ) https://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop-4.html
Let Rpnce = APIssinUserDLL_MsgBox(hWnd:=&H0, Prompt:="Yes, or No to ReCheck, Cancel for help ", Title:="Selection Check: Address is " & RcelsToYou.Address & " Value is """ & Valyou & """", Buts:=vbYesNoCancel) ' ' Pseudo Non Modal MsgBox
Set RcelsToYou = Selection: Let Valyou = RcelsToYou.Value: If IsArray(Valyou) Then Valyou = Valyou(1, 1) 'The code waited until you made one of the three message box options. But in this time you could change the selection object
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 --- you should get this HelpGetUpBollox.JPG imgur.com/KdKOYWr
If Rpnce = 7 Then GoTo Noughty ' Option to update the displayed Address and Value in Top Left cell of that range
End Sub

[DocAElstein]

New Post 17. It appeared after i copied all of full page 2 to make a full page 3. It is #post24895 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24895&viewfull=1#post24895)

It came from 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 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=10484&viewfull=1#post10484)



Section Rem 4 is the Function WinSubWinCls_JerkBackOffHooKterd
This picks out the specific event I want, my “Non modal message box” being activated, and changes the window dimensions, using the API thing SetWindowPos
( You will need to experiment about with, mainly, the 10 50 400 150 The other two numbers 0 40 you may want to adjust as well. Understanding those two numbers fully is a bit beyond me – it took me best part of a week to figure out WTF all the other stuff was about ).
The last thing this function does is “kill” or “drop” or ““take off” the “Hook”” or ““Unhook” the hook”. If you don’t do that the function seems to be triggered indefinitely.
A last thing on a similar point: Some other codes doing something similar that I saw, seemed to cause a wild recursion: The size adjustment done in the function seemed to set off the function code again. The stack seemed to be limited to 30. I could not see any reason to do that, and in fact it seemed to cause some weird inconsistent ghostly images to appear on my desktop. My function just does the thing that I think it should do once. It appears more stable. I did have a lot of fun braking things before I got the API stuff correct. But since then the code seems to work well without problems in a number of code situations on different computers and different Excel versions.
( In all the situations that I have tried, my code has Exited as I have expected after an If clause detects if the function is on the start of a first recursion run. ( I think it would probably do no harm to unhook directly after the SetWindowPos , just in case the SetWindowPos does not cause recursion. ( It seems to do no harm to unhook a few times) )



'_-=Rem 4============= Some hidden function / bookmark / bookmarks / cyber Robot thing was brought into life ("I hung or set a hook").
'That monitors events like my message box popping up. When it catches one it starts this finction and passes to it three parameters.
'_The first tells me with a number more exactly what event took place,
'_the next is the window identifying number of that window doing that particular event ,
'_ the last parameter is something maybe to do with the mouse god knows what exactly probably even Sid don't know... but looking at his Avatar I probably wouldn't say that to him as he I don't know if I would want to mess with him...
Private Function WinSubWinCls_JerkBackOffHooKerd(ByVal lMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long ' I "set a hook" which should trigger ( things similar to my Meassage box popping up , and gave it the AddressOf this function , 0 , and limited it to this "Thread" on my computer that is to say my Excel )
Let GlobinalCntChopsLog = GlobinalCntChopsLog + 1 ' The idea of this is that I add 1 on entering and subtract 1 when leaving the function. So this would be two if I started an other copy of this code before the first had finished. I am expecting that as the SetWindowPosition seemes to trigger it off again.
If GlobinalCntChopsLog = 2 Then Let GlobinalCntChopsLog = GlobinalCntChopsLog - 1: UnHookWindowsHookCodEx hHookTrapCrapNumber: Exit Function ' If I have 2 then that is an indication that recurtion has taken place, that is to say I started another function run caused by SetWindowPosition triggering it off. So i assume then that SetWindowPosition has done what it should so I can "take the hook off" (as if i did not then the function seems to get triggeredt indefinitely even without recusion), and then I exit the function. So I do expect a second copy of the code to run, but due to this it does not do anything other than take the "hook off". I also reduce the count by 1. It is then at 1. But then the first copy of the function ends from just under SetWindowPosition. So then the count is reduced again and is at the initial 0
If lMsg = 5 Then Let Booloks = SetWindowPosition(wParam, 0, 10, 50, 400, 150, 40) ' 5 here is the number for a window about to be activated. This is probably the one I want. (If I catch it when it is starting , 3, then It might then re set the size and position stuff again to the standard after I have done it)
Let GlobinalCntChopsLog = GlobinalCntChopsLog - 1 ' Every first copy run of the code has the count reduced to 0 so that when it starts again (as the only first copy active) it will be increased to 1 again to indicate it is a run of the function copy 1
End Function

[DocAElstein]

New post 18. It appeared after I copied all of full page 2 to make a new full page 3 It is #post24896 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24896&viewfull=1#post24896)
It came from New post #14, 31 Oct 2024, it came from the original post #12 when the original posts 11 12 and 13 where copied on 31 Oct 2024
#post24887 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24887&viewfull=1#post24887)

[DocAElstein]

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/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=10485&viewfull=1#post10485 as it takes you to page 3 , the link made from the page 2 link , https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function/page2#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

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.

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 SubSo 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/development-testing-forum/1215283-gimmie-da-codexamples-call-in-the-appendix-posts-2018-no-reply-needed-but-if-u.html#post4822413 )




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

_._______________________________


_._______________________________

[DocAElstein]

Now Post 20 after I made a full page 3 from copying a full page 2. The previous link, https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24888&viewfull=1#post24888 , seems to be broken, taking you to page 3. The one made from the page linkk, https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function/page2#post24888 , seems OK

It was New post #16, 31 Oct 2024, it came from the original post #13 when original posts 11 12 and 13 where copied on 31 Oct 2024
#post24888 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24888&viewfull=1#post24888)

[DocAElstein]

This is now post 23, after a copy of full page 2 gave me a full page 3
It was First copy from New post #16, all done on 31 Oct 2024
#post24889 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24889&viewfull=1#post24889)



A run of Private Sub MainSubWithAllOtherStuffInIt()


We need to make a minor change, temporarily, so as to be able to run the main macro from the spreadsheet: Change the Private to Public
https://i.postimg.cc/rwRwrPxm/Change-Private-of-main-coding-to-Public-temporarily.jpg

Now, we can close the VBEditor and concentrate on the spreadsheet.

Select some arbitrary cell with any value in it: https://i.postimg.cc/hG94CQCR/Select-an-arbritrary-cell-with-any-value-in-it.jpg


Take the - View - Macros - ribbon route,
https://i.postimg.cc/3wMJCFFZ/View-the-macros-via-ribbon.jpg
, ( or the short cut key combination of holding the Alt key then selecting key F8 ) , to get the macro dialogue pop up

Select Sub MainSubWithAllOtherStuffInIt() and then the Run Button: https://i.postimg.cc/PrRqNjL9/Run-Sub-Main-Sub-With-All-Other-Stuff-In-It.jpg


The message box pop up should now prompt you for for a response: https://i.postimg.cc/bNJJzJv1/Prompted-for-answer.jpg

Let us say , just as a demonstration example , that you did not want that cell that is selected. So, ignore the prompt initially, and make another selection: https://i.postimg.cc/gc4jFRGY/Make-an-alternative-selection.jpg (This should be possible, since, unlike the standard VBA message box pop up, our pop up is non modal, which means you can still select things whilst the pop up is showing)

The message box pop up will not have changed as a result od you making a new selection, , and will it still be suggesting / asking you if you wanted the previous selection. So answer No n https://i.postimg.cc/MHsp7k9c/Answer-No.jpg

The message box pop up should then re appear almost immediately indicating that you have the new selection:
https://i.postimg.cc/0j5NK50R/Message-Box-re-appears-almost-immediately-and-indicates-the-new-selection.jpg

So answer Yes https://i.postimg.cc/Jn5nhHJ5/Answer-Yes.jpg



The demonstration is finished at this point. This is just a demonstration, so nothing useful was done. However, in a practical coding, one that then continued, you would have now your second selection as a range object represented by the variable RSel , - To achieve that is the main purpose of this Thread


Please remember to change back the main coding to Private, before following any further posts in this Thread, (or alternatively disregard the file you have, and re download it)

[DocAElstein]

This is now post 21
It was a Second copy from New post #16, 31 Oct 2024, all done on 31 Oct 2024
#post24890 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24890&viewfull=1#post24890)



Page 3 Simplified explanation
https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function/page3
https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function/page3
Post 21 ( Forum Post #post24890 )
https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24890&viewfull=1#post24890
https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24890&viewfull=1#post24890
https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function/page3#post24890
https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function/page3#post24890






Simplified Walkthrough coding explanations
The detailed explanations in the last two pages ( https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function
https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function/page2 ] ) are rather rich and intense, such that they need a lot of time and consideration and its easy to get bogged down and miss seeing the wood for the trees (https://dictionary.cambridge.org/dictionary/english/can-t-see-the-wood-for-the-trees)

This page 3 ( https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function/page3 ) will be shorter simplified explanations based : step debug mode *step through the code lines; a brief simple run ; or general short explanation

( * F8 keyboard key from the VBEditor, (after selecting anywhere in the coding you want to step through, which in our case is the main Sub. That will let you step line by line through the coding. In this node a number of other useful tools are available, just one of which is that hovering over a variable, or some simple expressions, may cause a small pop up to appear telling you of the contents. - https://i.postimg.cc/tCf4krzW/Step-debug-F8-mode-in-main-Sub.jpg )


Post 22 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24891&viewfull=1#post24891)
A debug F8 step mode full walkthrough the codings so far discussed in detail

Post 23 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24889&viewfull=1#post24889)
A simple run of the codings so far discussed in detail

Post 24 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24897&viewfull=1#post24897)
The issue of ByRef or ByVal is discussed, in terms of changing the typically needed and used ByRef to ByVal

Post 25 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24898&viewfull=1#post24898)
A simple run of the codings with the change to ByVal

[DocAElstein]

This is now post 22 after a copy of full page 2 got me a new full page 3
It was Taken from the Second copy from New post #16, 31 Oct 2024, all done on 31 Oct 2024
#post24891 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24891&viewfull=1#post24891)



Explanations to coding in module AlansInputBoxComnutByRef

Step through the coding to explain it
Open the Visual Basic Editor ( When in the spreadsheet Hold Alt key and press key F11 )

We can identify 4 main coding sections.
https://i.postimg.cc/Y9Tw5zhn/4-main-coding-sections.jpg
https://i.postimg.cc/Y9Tw5zhn/4-main-coding-sections.jpg (https://postimg.cc/LYk7ffkL)



The second section is the coding we actually run. That coding would in a real life example likely be somewhere else, and almost certainly part of a larger coding, ( as we do in the last 4 post explanations) The other 3 sections would most likely, for the sake of tidiness, be kept in their own coding module. They are basically the main clever background API related stuff

Click anywhere in Private Sub MainSubWithAllOtherStuffInIt(),
Press continually the F8 key to step through line by line.
https://i.postimg.cc/SKZ5jnXb/Step-debug-F8-mode-in-main-Sub.jpg
We pass Dim RSel As Range , without hanging there, (as declare type code lines such as that are pseudo already done in VBA coding). It is necessary to have been done, because we pass that range variable ( which contains a pointer to the range object) ByRef. (That "by referable" , ByRef , to the variable type passing is determined by the signature line in the next routine, Private Sub HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp( ByRef RcelsToYou) )


Passing Call HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp(RSel) , takes us, as we typically expect in VBA, into Private Sub HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp(ByRef RcelsToYou) '
https://i.postimg.cc/L6zWjQ4C/passing-into-Private-Sub-Hang-AHook-To-Catch-APIssin-User-DLL-Msg-Box-Then-Bring-That-Msg-Box-Up-By-Ref-Rcels-To-Yo.jpg
6097





Passing Set RcelsToYou = Selection effectively makes the variable in the main Sub, RSel point to the selected range in the active spreadsheet ( Active Cell :- http://www.eileenslounge.com/viewtopic.php?p=313746&sid=0fce2674629d25026e7de638662b3811#p313746
http://www.eileenslounge.com/viewtopic.php?p=313746#p313746 )

( Passing Let BookMarkClassTeachMeWind = 5 allows us later to use a variable when we want to use a 5 , the reasons form that are explained in the extended explanations).

Passing Let hHookTrapCrapNumber = SetWindowsHooksExample(BookMarkClassTeachMeWind, AddressOf WinSubWinCls_JerkBackOffHooKerd, 0, GetDaFredId) arranges that the final routine, Private Function WinSubWinCls_JerkBackOffHooKerd(ByVal lMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long ' is likely to be set off when certain things, "events", happen. Importantly for us, one of those things, "events", is when we use the available non modal pop up, "MessageBoxA". (That "MessageBoxA" pop up., is something similar to the standard available VBA MessageBox , and this extra one is effectively made available to us in the first declaration line. The main difference, that is important for us, is that the "MessageBoxA" is non modal, meaning in simple terms that we can make a selection in the spreadsheet whilst it is showing. That is not possible with the standard VBA MessageBox )
Crudely, simplified in Laymen terms, I have set a hook that may catch some things

( The next set of code lines,
Dim Valyou As Variant: Let Valyou = RcelsToYou.Value: If IsArray(Valyou) Then Valyou = Valyou(1, 1)
, are not a main part of the important coding, and are only needed for the specific implementation. In this specific implementation, at some point later, I show the selection value of the range selected. This set of code lines ensures I do not error if attempting to show a single value when the user has selected more than one cell. If that was done, then I show just the top left cell value. )

We now pass Let Rpnce = APIssinUserDLL_MsgBox( hWnd:=&H0, Prompt:="Yes, or No to ReCheck, Cancel for help ", Title:="Selection Check: Address is " & RcelsToYou.Address & " Value is """ & Valyou & """", Buts:=vbYesNoCancel ) ' . Ordinarily in a more simple use of the non modal MessageBoxA, this would bring up our Message Box pop up. But that does not immediately happen because we set a hook which amongst other things catches things that may go on when the dll coding set of by MessageBoxA, runs. (More technically speaking, I have done a bit of sub classing or made a form of custom dll of my own).
My hook has been pulled/aroused, which sets off Function WinSubWinCls_JerkBackOffHooKerd( ByVal lMsg As Long, ByVal wParam As Long, ByVal lParam As Long ) As Long '
https://i.postimg.cc/TP6d9sCY/passing-into-Function-Win-Sub-Win-Cls-Jerk-Back-Off-Hoo-Kerd-By-Val-l-Msg-As-Long-By-Val-w-Param-As-Long-B.jpg
6098

The main purpose of Function WinSubWinCls_JerkBackOffHooKerd( [ color=Blue]ByVal[/color] lMsg As Long, ByVal wParam As Long, ByVal lParam As Long ) As Long ' can in simple terms be regarded as modifying some dimensions of a window that is opening. In other words we caught our MessageBoxA opening, and mess with some of its default dimensions. That is what these figures are about (wParam, 0, 10, 50, 400, 150, 40)
There are a couple of snags, _(a) and _(b), one expected, _(b), and one that only I have noticed, _(a).
_(a) When I do the code line SetWindowPosition(wParam, 0, 10, 50, 400, 150, 40) , an unexpected/ unwanted occurrence is that the function is set off again, that is to say a copy of it starts before the initial copy finishes. This is allowed in VBA coding and is basically the phenomena known as recursion. (Often this can go on almost for ever, before the famous stack overflow error occurs when your computer can no longer cope with storing things related to the many copies that are started but never finished. Strangely in this case, it seems to be stopped at a certain number of copies, so it is not such a terminal problem. Never the less it is undesirable. So I have a global variable, GlobinalCntChopsLog which has the effect of keeping track of the copy number currently running. (This is achieved by increasing it by 1 when a copy starts, and decreasing it by 1 when the copy finishes.
So the second line in the, If GlobinalCntChopsLog = 2 Then …………. effectively allows us to do the necessary terminations, when the function has done once already what we want, which is the dimension related stuff, (wParam, 0, 10, 50, 400, 150, 40)
_(b) Not all things that are caught by the hook and so set off this function are the thing which allow the (wParam, 0, 10, 50, 400, 150, 40) to do what we want. The If lMsg = 5 Then makes the correct event. (It is a bit too subtle point for this simplified explanation, but 5 coming into the function via BookMarkClassTeachMeWind from the previous routine does not always follow that a 5 would be used here. )

We may have got a bit lost in the trees here. So lets us go back a bit to ………My hook has been pulled/aroused, which sets off Function WinSubWinCls_JerkBackOffHooKerd( ByVal lMsg As Long, ByVal wParam As Long, ByVal lParam As Long ) As Long '
https://i.postimg.cc/TP6d9sCY/passing-into-Function-Win-Sub-Win-Cls-Jerk-Back-Off-Hoo-Kerd-By-Val-l-Msg-As-Long-By-Val-w-Param-As-Long-B.jpg ……

If you step through, you will see that the function is done a few times. This is perhaps because we are either catching many things or, as the more detailed explanations suggest, we may actually have a few hooks catching things. They do not all meet the requirement of the If lMsg = 5 Then ….. so not much is done. (In these repeated runs of the function, we ended the functions normally, so GlobinalCntChopsLog is 1, so we do not encounter the recursion phenomena discussed )
Eventually after about 6 goes, the If lMsg = 5 Then ….. criteria is satisfied. The main …. (wParam, 0, 10, 50, 400, 150, 40)…. is then done, but you will notice that doing that seems to set off the function again, but if you continue to step through and hover over the variable GlobinalCntChopsLog, you will see that it is 2, so we have detected this, https://i.postimg.cc/DwRnkxV9/Hover-over-to-see-Globinal-Cnt-Chops-Log-is-at-2.jpg 6099
By virtue of that 2, the termination of the current running function is done with a necessary removal or the hook or hooks, or similar done by the UnHookWindowsHookCodEx hHookTrapCrapNumber
We return then into the previous function, Sub HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp(ByRef RcelsToYou) ' , which initially has the effect of the line which triggered Function WinSubWinCls_JerkBackOffHooKerd( ByVal lMsg As Long, ByVal wParam As Long, ByVal lParam As Long ) As Long completing ,which attempted to, and so does now , bring up the pop up. So it should then, indeed, pop up https://i.postimg.cc/s2hcw4Rf/Popped-up.jpg 6100
After making a selection, you will come to the next line in the main Sub of Set RcelsToYou = Selection …………….. https://i.postimg.cc/DZTbKt8D/Completed-pop-up.jpg 6101


The rest is fairly self explanatory:- Basically once you are happy that you have made the correct range selection, this routine, Sub HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp(ByRef RcelsToYou) , ends and you are back into the main coding, and have the range object of your choice in the variable RSel

[DocAElstein]

This is a new post 24. It appeared after a copy of full page 2 gave me a full page 3. It is #post24897 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24897&viewfull=1#post24897)
It came from Original post #13, it got shifted down to post #15 when posts 11 12 and 13 where copied on 31 Oct 2024 #post10485 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=10485&viewfull=1#post10485)


This is almost identical to post #24 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24891&viewfull=1#post24891)
In fact we only really need 1 change, in the signature line of the routine first set off/Called by the main coding, but two other changes will be made to aid in the explanation

Set RSel = Selection ' To aid in the explanation of the ByVal example
Call HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp(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 & "" & vbCr & vbLf & "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
RSel.Select ' To aid in the explanation of the ByVal example
End Sub ' Typically this is your main program End Sub
'Private Sub HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp(ByRef RcelsToYou) ' This will by referral To You, (RSel), the actual Pointer of you the original RSel. This is not too important a point here, but intersting if you consider the next line alternative to this one.....
Private Sub HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp(ByVal RcelsToYou) ' The RSel Pointer aint Gone anywhere if you do this. Just a copy of the Pointer is here. This will allow you to change the value as the Pointer or a copy of it will tell you where to go and do that... But in neither this line or the last line case have you sent the range object. If you use this line then you will find that the address of the range object will not change, as that refers to the range object of the copy variable in this subroutine. But that will not change the range object of RSel The full coding is in the code module, AlansInputBoxComnutsByVal , in the uploaded file, InputBoxMessageBoxPopUpBugsAPI.xls

Explanation of important difference to the ByRef example.
(https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24891&viewfull=1#post24891)Since the main change is so minimal, it is probably more useful to explain the important effects of the difference rather than repeat most of what was in the previous explanation (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function/page3#post10485):
We have filled/assigned the variable RSel to the current selection ** in the main sub to the existing selection, with the first extra line - Set RSel = Selection - added to aid in the explanation, and we do this just before the coding goes off into the API related things.
The first thing that then is done in the initial API coding is that a code line - Set RcelsToYou = Selection - does effectively very similar to our first extra code line. However, the main point that will be emphasised many times in such explanations, is that the second time we are doing something important a bit different: RcelsToYou is no longer referring to the variable RSel. It will at this point, point to the same range object assigned to RSel, because it is a copy of the pointer. But as we proceed through selections via our pop up in the course of running normally all the coding, we will no longer be actually changing the range object to which RSel refers/points to.

Just before the end of the main coding , the other extra code line - RSel.Select - will now select our very first selection, regardless of any selections we made during the course of our pop up in the course of running normally all the coding.
The end result rather goes against the main purpose of this thread, which is to have the range object referred to/ pointed to by the variable RSel changed as a result of a selection. But it emphasises the need to have ByRef in the coding to achieve our main original requirement

( ** Note that if we had not had the first extra code line, then the final extra code line would actually have errored, since RSel would not have been assigned a range object. For the same situation in the previous ByRef coding example, there would be no error as the variable RSel was effectively being referred to and assigned one or more times course of our pop up in the course of running normally all the coding. )

[DocAElstein]

This is a new post 25, appearing after a copy of full page 2 got me a new full page 3 It is #post24898 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24898&viewfull=1#post24898)
It came from New post #16, 31 Oct 2024, it came from the original post #13 when original posts 11 12 and 13 where copied on 31 Oct 2024
#post24888 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24888&viewfull=1#post24888)




Working example for the ByVal case
We will do a practical example that helps show the important difference between the ByVal and ByRef codings. (Although it should be noted that there are many simpler ways to achiever the same without the complicated API stuff)

We will use exactly the same demo coding as in the last post (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24897&viewfull=1#post24897) The full coding is in the code module, AlansInputBoxComnutsByVal , in the uploaded file, InputBoxMessageBoxPopUpBugsAPI.xls

I need to make the same minor temporary change to the main coding, just as I did in the last post. As previously, this is just so that I can access the macro from the spreadsheet: Please change the Private to Public in the main.
https://i.postimg.cc/J08KY7rK/Change-Private-of-main-coding-to-Public-temporarily.jpg

We can close not the VBEditor, and concentrate on the spreadsheet.



Lets say we have a long list, which in the practice could be very long. Lets say I want to work down the list , changing just some cell values.


Take the - View - Macros - ribbon route,
https://i.postimg.cc/3wMJCFFZ/View-the-macros-via-ribbon.jpg
, ( or the short cut key combination of holding the Alt key then selecting key F8 ) , to get the macro dialogue pop up

Select Sub MainSubWithAllOtherStuffInIt() and then the Run Button: https://i.postimg.cc/LsrxfBGc/Run-Sub-Main-Sub-With-All-Other-Stuff-In-It.jpg


Select a cell towards the top of the list,
https://i.postimg.cc/ydcvT3Hk/Select-a-value-towards-the-top-of-the-list.jpg
, make a change, such as dragging a value down.
https://i.postimg.cc/rpdhKYZG/Make-some-change-such-as-draging-the-value-down.jpg


Make some similar change a bit further down, such as dragging a value up.
https://i.postimg.cc/QdY4Nrmx/Selectin-No-gives-you-range-value-details.jpg

If now you select No on the message box pop up, then your top left selection value will be shown, as well as the range selection address.
https://i.postimg.cc/QdY4Nrmx/Selectin-No-gives-you-range-value-details.jpg

If you now select Yes on the message box pop up, you will be taken back to your original selection, (and the message box goes away)
https://i.postimg.cc/WbnYKZJZ/Selectin-Yes-brings-you-back-to-the-original-selection.jpg


The final selection of the original cell was caused by the last extra code line, RSel.Select , in the main sub, because in that Main sub, the range referred to in RSel is the original set cell selection. The variable was not referred to by the routine, Sub HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp(ByVal RcelsToYou), as the variable, RcelsToYou , was instructed in that signature line, ( Sub HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp(ByVal RcelsToYou) ) to just take a copy*, ( *in the case of a range object, (as with all objects), it is the copy of the pointer to a range object




(Please remember to change back the main coding to Private, before following any further posts in this Thread, (or alternatively disregard the file you have, and re download it)

[DocAElstein]

This is a new post 26, appearing after a copy of a full page 2 gave me a full page 3. It is #post24899 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24899&viewfull=1#post24899)
It came from Second copy from New post #16, 31 Oct 2024, all done on 31 Oct 2024
#post24890 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24890&viewfull=1#post24890)

[DocAElstein]

This is a new post 27, appearing after a copy of full page 2 gave me a full page 3. It is #post24900 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24900&viewfull=1#post24900)
It came from It was Taken from the Second copy from New post #16, 31 Oct 2024, all done on 31 Oct 2024
#post24891 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24891&viewfull=1#post24891)



API stuff tidily away in separate module
So far we have had all the 4 main coding sections in one code module. This helped to keep things fairly compact for the explanations, especially the code walk through which went neatly from the main sub down to the other two needed routines, then back to finish at the main sub.
It would also be Ok in a practical usage with a much larger main sub to do this, although then the main sub might be brought down to be the last, just for the sake of tidiness to keep most of the extra APIU stuff together at the top.

Another possibility would be to keep most of the API stuff in a separate module.
Doing this requires only 2 minor modifications.
_ The main sub will of course be removed to another code module
_ The only other modification is that the Called routine ,
Private Sub HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp(ByRef RcelsToYou)
, will need to be made Public or else it cannot be Called from outside the module in which it is in
Public Sub HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp(ByRef RcelsToYou)
The other things are only referenced from within the module, so they can be left Private, which is good practice to do so as to give better flexibility to naming other things outside the module

Since we have many comments and detailed explanations already, I will also remove the comments for clarity for this iteration of the coding development. As mentioned, the detailed commented codings are netter suited for the all in one code module, such as the module AlansInputBoxComnutsByRef


I will make a new code module, for the API stuff, called InpBxAPIddllWindowsSubClassing, and these will be the three coding sections in it



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

Private Declare Function SetWindowsHooksExample Lib "user32" Alias "SetWindowsHookExA" (ByVal Hooktype As Long, ByVal lokprocedureAddress As Long, Optional ByVal hmod As Long, Optional ByVal DaFredId As Long) As Long
Private hHookTrapCrapNumber As Long

Private Declare Function GetDaFredId Lib "kernel32" Alias "GetCurrentThreadId" () As Long

Private Declare Function UnHookWindowsHookCodEx Lib "user32" Alias "UnhookWindowsHookEx" (ByVal hHookTrapCrapNumber As Long) As Long

Private Declare Function SetWindowPosition Lib "user32" Alias "SetWindowPos" (ByVal hWnd As Long, ByVal zNumber As Long, ByVal CoedX As Long, ByVal CoedY As Long, ByVal xPiXel As Long, ByVal yPiYel As Long, ByVal wFlags As Long) As Long
Dim Booloks As Boolean
Dim GlobinalCntChopsLog As Long
'_________________________________________________ ___________________________________


Public Sub HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp(ByRef RcelsToYou)
Set RcelsToYou = Selection
Noughty:
Dim BookMarkClassTeachMeWind As Long: Let BookMarkClassTeachMeWind = 5
Let hHookTrapCrapNumber = SetWindowsHooksExample(BookMarkClassTeachMeWind, AddressOf WinSubWinCls_JerkBackOffHooKerd, 0, GetDaFredId)
Dim Valyou As Variant: Let Valyou = RcelsToYou.Value: If IsArray(Valyou) Then Valyou = Valyou(1, 1)
Dim Rpnce As Long
Let Rpnce = APIssinUserDLL_MsgBox(hWnd:=&H0, Prompt:="Yes, or No to ReCheck, Cancel for help ", Title:="Selection Check: Address is " & RcelsToYou.Address & " Value is """ & Valyou & """", Buts:=vbYesNoCancel)
Set RcelsToYou = Selection: Let Valyou = RcelsToYou.Value: If IsArray(Valyou) Then Valyou = Valyou(1, 1)
If Rpnce = 2 Then Application.Help HelpFile:=ThisWorkbook.Path & "\AnyFileName.chm", HelpContextID:=2
If Rpnce = 7 Then GoTo Noughty
End Sub
'_________________________________________________ __________________________________


Private Function WinSubWinCls_JerkBackOffHooKerd(ByVal lMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
Let GlobinalCntChopsLog = GlobinalCntChopsLog + 1
If GlobinalCntChopsLog = 2 Then Let GlobinalCntChopsLog = GlobinalCntChopsLog - 1: UnHookWindowsHookCodEx hHookTrapCrapNumber: Exit Function
If lMsg = 5 Then Let Booloks = SetWindowPosition(wParam, 0, 10, 50, 400, 150, 40)
Let GlobinalCntChopsLog = GlobinalCntChopsLog - 1
End Function







In any code module requiring the pop up, these would be the required code lines

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
Call HangAHookToCatchAPIssinUserDLL_MsgBoxThenBringThat MsgBoxUp(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"

[DocAElstein]

This is a new post 28, appearing after a copy of a full page 2 gave me a full page 3 It is now #post24901 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24901&viewfull=1#post24901)
It came from It came from First copy from New post #16, all done on 31 Oct 2024
#post24889 (https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24889&viewfull=1#post24889)

[DocAElstein]

This was Post #20, but when I copied all of page 2, it became post #29 https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function/page3#post24892
https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function/page3#post24892
https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24892&viewfull=1#post24892
https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24892&viewfull=1#post24892
The second link still worked, the first needed me to change the page 2 to page 3, which makes sense. (Otherwise it went to the top of page 2)

[DocAElstein]

This is post 30, it came from post … well …post 29 that was post 20. So effectively it came from the post above
29 https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function/page3#post24902
https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function/page2#post24902
https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24892&viewfull=1#post24902
https://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=24902&viewfull=1#post24902
links are OK