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
Declareing Declare Type FunctionsCode: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
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/showth...0478#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
Reply With Quote
Bookmarks