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

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

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

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

Code:

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