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

**Rick Rothstein** · 05-30-2014, 12:13 AM

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).

**LalitPandey87** · 06-26-2014, 11:39 AM

Cool

**DocAElstein** · 02-04-2018, 01:48 AM

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/