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

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

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

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

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

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

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

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

Code:

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