Results 1 to 3 of 3

Thread: How To React To The Cancel Button in a VB (not Application) InputBox

  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13

    How To React To The Cancel Button in a VB (not Application) InputBox

    This posting is "short and sweet" as I think the below code snippet is self-explanatory (even though the StrPtr function may be new to you)...
    Code:
    Sub TestMe()
      Dim Answer As String
      '....
      '....
      Answer = InputBox("Tell me something")
      If StrPtr(Answer) = 0 Then
        MsgBox "The user clicked Cancel, so we will exit the subroutine now."
        Exit Sub
      ElseIf Len(Answer) = 0 Then
        MsgBox "The user clicked OK without entering anything in the InputBox!"
      Else
        MsgBox "The user entered the following..." & vbLf & vbLf & Answer
      End If
      '....
      '....
    End Sub
    Simply replace the MsgBoxes with any code you might want to execute for the stated outcome. To get a better feel for what is going on, copy/paste the above code into a general Module (Insert/Module from the VB editor menu bar) and run it.... first click the OK button without entering anything, then type something in and click the OK button, then finally click the Cancel button (with or without text in the type-in field).
    Last edited by Rick Rothstein; 05-30-2014 at 02:26 AM.

  2. #2
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    13
    Cool

  3. #3
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    A super last gem of information to add to my last revision of Input Boxes, Thanks Rick


    http://www.excelfox.com/forum/showth...0462#post10462





    Edit… and shortly after I used it again to improve something
    http://www.excelfox.com/forum/showth...number-problem
    So once again a really useful Tip…. But having thought about it… _...
    _....with respect, I don’t think it is self explanatory, well not to me Lol..
    This it my take on:
    String variable:
    This is a variable at the Dim stage a "Pointer" to a "Blue Print" (or Form, Questionnaire 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 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. Instructions will tell how to do this.
    So at the address of the variable itself there is only a pointer to a memory area.
    StrPtr
    The StrPtr function “returns the address of start of that actual memory area once used in which the Unicode string is stored”. This address is not identical to the address of the variable that you use this string in Visual Basic: - at the address of the variable itself there is only a pointer to a memory area..
    The actual memory address ( for the start of the string ) may change, but it will not have been set the first time if the variable has not yet been used.
    API ddl stuff
    I expect the Functions like the StrPtr function are “WRapper functions for some API Function”. In English I think that means that that StrPtr function gives the necessary and appropriate arguments for an API Function that can return addresses of variable by running a code hidden somewhere in a File usually with a .dll extension which is usually supplied as standard with windows. The idea being that these files are a sort of directly runtime executable things that many other applications, such as Excel VBA can use in a code run. It is some sort of efficient way to allow the linking to be done to the file and the various functions loaded and used as the code such as the VBA code runs. Hence a name direct link library – The file is some special sort of runnable thing rather than just a collection of function codes. It is somehow directly Integratable into a code, as apposed to an earlier way of doing things known as static linking which had to copy a lot of stuff into a code to be used when the code ran. The word API is just a word used to intimidate. It stands for Application Program Interface. It very loosely means the things available to an Application programmer from these libraries or some similarly available coding stuff. The word Interface is often used in computing to mean the bit someone can interact with which has all the other clever stuff behind it. It is intend to give some unity / consistency / standardisation of the bit people use to do more complicated stuff that might not be directly available or might be require a lot more in depth computer knowledge to get to and use directly. If you like API is the control panel.
    These things are often a bit vague as I doubt anyone knows in the meantime what is actually going on. You may actually be using the “Excel API” when you play around with Data Base things which use stuff that takes Objects, Linking and Embedding them ( OLE ) into a VBA code. ( you use OLEDB library stuff )
    Anyway the end result of all this is that the actual address, ( as a Long number I expect ) is retuned by the API function and that passed to the wrapper function StrPtr
    Very likely the API function or the wrapper function is wired to return 0 if the things somehow does not work, or doesn’t find anything or whatever similar.
    Hence the end result of all this is I will have
    ____________StrPtr(MyVaraibleNotYetUsed)=0
    _________________________________________if adeptly named variable, MyVaraibleNotYetUsed , is not yet used






    https://powerspreadsheets.com/excel-vba-inputbox/
    Last edited by DocAElstein; 02-28-2018 at 02:28 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. InputBox OK and Cancel Button Problem
    By mackypogi in forum Excel Help
    Replies: 5
    Last Post: 05-30-2014, 12:20 AM
  2. Replies: 2
    Last Post: 11-02-2013, 04:17 PM
  3. Replies: 4
    Last Post: 07-10-2013, 04:35 AM
  4. Application.Caller
    By Transformer in forum Familiar with Commands and Formulas
    Replies: 0
    Last Post: 05-01-2013, 12:32 PM
  5. Print VBA cancel command not working
    By Tony in forum Excel Help
    Replies: 1
    Last Post: 02-07-2013, 07:09 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
  •