CODEWITHSUNDEEP
vbainputuser-controls
levraininjaneer
levraininjaneer

Reputation: 1377

How to tell the difference between "cancel" and "OK" with an empty textbox of an InputBox?

A VBA inputBox returns an empty string when the cancel button is pressed.
When OK is pressed, it the text in its textbox.
The inputBox's third positional argument is the initial text in its textbox. The default value for this argument is "".

I use an inputBox to ask a user to specify the name for a new record when he clicks an "Add Record" button. If he presses "cancel", I exit the sub.

If he fails to enter a name, or enters one and deletes it, I want a msgBox to tell him he must specify a unique record name.

How do I tell the difference between "cancel" and "OK" with an empty textbox?

I found several similar questions but none solved my problem.

Upvotes: 3

Views: 4322

Answers (3)

Lorien Asin Alegre
Lorien Asin Alegre

Reputation: 1

thanks for helping me along the time... here you are. In this case is a textbox of YES and NO. If you want OK and cancell, you will have to see this: my case is vbYesNo, yours will be vbOKCancel OK return value is: 1 Cancel return value is: 2

Dim control As String
control = Empty

control = MsgBox("Are you sure ??", vbYesNo, "System warning")
If control = 6 Then
    ' code if answer was YES
ElseIf control = 7 Then
    ' code if answer was NO
End If

Upvotes: 0

Jamie Riis
Jamie Riis

Reputation: 411

I believe that the code below will provide you a robust solution that works. More information the Application.InputBox method and function can be found at Microsoft Docs.

Option Explicit

'Type Parameter of the Application.InputBox method tells
'the method to return a value of that type.  Below I've shown
'having it return a text string
Public Const A_TEXT_STRING As Variant = 2

Sub Button1_Click()
    Dim myVal As Variant

    myVal = Application.InputBox("Enter Record Name", _
                           Title:="RECORD NAME", _
                           Type:=A_TEXT_STRING)
    'If myVal equals the vbNullString type then
    'the person pressed OK without entering any data
    If myVal = vbNullString Then
        MsgBox "Please Select a record", Buttons:=vbOKOnly, Title:="SELECT RECORD"
    'if myVal is equal to False (boolean value) then the user pressed
    'the Cancel button.  By checking for not equal, that means that the user pressed
    'Ok and entered a value.  You'll need to handle the condition of valid values
    ElseIf myVal <> False Then
        Range("A1").Value2 = myVal
    End If
End Sub

Upvotes: 1

Kirszu
Kirszu

Reputation: 364

There is a way to check if user clicked "Cancel" or just entered empty string. Try this:

test = InputBox("Enter a value")
If StrPtr(test) = 0 Then
    MsgBox "Cancel"
ElseIf Len(test) = 0 Then
    MsgBox "No entry"
Else
    MsgBox "Correct"
End If

However it is quite sketchy solution. You can read more about StrPtr function here: What are the benefits and risks of using the StrPtr function in VBA?

Upvotes: 3

Related Questions