Results 1 to 10 of 13

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

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #10
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    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
    Last edited by DocAElstein; 02-09-2018 at 03:05 AM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

Similar Threads

  1. How To React To The Cancel Button in a VB (not Application) InputBox
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 2
    Last Post: 02-04-2018, 01:48 AM
  2. Replies: 2
    Last Post: 02-12-2016, 04:32 PM
  3. InputBox OK and Cancel Button Problem
    By mackypogi in forum Excel Help
    Replies: 5
    Last Post: 05-30-2014, 12:20 AM
  4. VBA To Display Pop Up Alert When Duplicate Entry Is Made
    By peter renton in forum Excel Help
    Replies: 20
    Last Post: 07-26-2013, 07:56 PM
  5. Number validation in Text Boxes VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 2
    Last Post: 05-17-2012, 02:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •