CODEWITHSUNDEEP
excelvba
4 Leave Cover
4 Leave Cover

Reputation: 1276

EXCEL VBA InputBox Ok/Cancel

Basically I need 2 inputbox. First inputbox will ask user to input a value and then prompt the 2nd inputbox after user click ok. The default value in the 2nd inputbox will be the value one cell to the right of the value in the first inputbox. This is what I want to achieve but the problem with inputbox is that the Cancel button which throw me error message or won't exit sub by any means.

So are there any other similar approaches in achieving my goal? Thanks a lot in advance!

Upvotes: 0

Views: 29307

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149287

Since it is the values that you want, don't use the InputBox. Use the Application.Inputbox. If you press the magic key F1 in Excel and you type Application.Inputbox then you will see some magic text appear out of nowhere ;)

Here is an example usage.

Ret = Application.InputBox(prompt := "Please enter a value", type := 1)

Notice the type 1.

I can give you an exact answer but I am sure this will get you started ;) If not then post back :)

Upvotes: 0

James
James

Reputation: 2453

InputBox(...) returns an empty string "" whenever the cancel button is pressed.

Knowing this, you could check to see if the input is "" for each input. If at any point along the way you encounter a cancel, you don't go any further.

This is demonstrated below using nested if statements.

Sub Macro1()
    Dim x As String
    Dim y As String
    Dim yDefault As String

    x = InputBox("Prompt One", "TITLE ONE")

    If (x <> "") Then
            yDefault = GetDefaultValue(x)
            y = InputBox("Prompt Two", "TITLE TWO", yDefault)

        If (y <> "") Then
            MsgBox "X: " & x & vbCrLf & "Y: " & y, vbOKOnly, "RESULTS"
        End If
    End If
End Sub

Function GetDefaultValue(x As String) As String
    ' Your custom logic to grab the default value (whatever cell) goes here
    GetDefaultValue = "DEFAULT(" & x & ")"
End Function

Upvotes: 2

Related Questions