Trouble with InputBox

[Juggler_IN]

I am capturing a value using a InputBox command. The problem is that if the input is 0, the input box returns FALSE and if I press cancel it still returns FALSE as output but with Runtime error 13, Type mismatch. My code for cancel is working because of both outputs being FALSE.

How to go back to GetM in case of a Cancel button?

GetM:
M = Application.InputBox("Method: 0, 1; (Default=0)", "Input", False, , , , , 4)
If M = False Then
MsgBox "Cancel button clicked, Input Box will close.", , "Cancel was clicked."
Range("A1").Select
Exit Sub
ElseIf M = Empty Then GoTo GetM
Else
End If

 

[NARAYANK991]

Hi ,

The problem is that the last parameter of the Application.InputBox method has been chosen as 4 ; this allows for only 2 values TRUE or FALSE. TRUE and 1 are equivalent , while FALSE and 0 are equivalent.

The default value that you have chosen for the method is FALSE or 0.

Thus , whether the user enters 0 and clicks the OK button , or presses the CANCEL button , the effect is the same.

The only possibility is either the user enters 1 and then clicks OK , or you change the default to TRUE so that the user just clicks OK.

 

[mikerickson]

It looks like you want there to be one of three responses from the user 0, 1 or cancel.
Your InputBox is set to type 4, boolean, which only accepts two options.

The way to fix that is to use type 5, which accepts numbers and boolean.

Your test If M = False is running into Excel's overly helpful automatic type conversion. M = False can't distiquish betwen M being 0 or False. Testing the data type of M will address that.

Dim M As Variant

M = Application.InputBox("enter 0 or 1", Default:=0, Type:=5)

If TypeName(M) = "Boolean" Then
    MsgBox "Cancel button clicked, Input Box will close.", , "Cancel was clicked."
    Range("A1").Select
    Exit Sub
Else
    MsgBox "user entered " & M
    
End If

 

[Juggler_IN]

@mikerickson,

With your code Cancel is working, but if:

1. InputBox is EMPTY, then [ElseIf M = Empty Then GoTo GetM] is not working.
2. If the InputBox is inputted with 0 then it is is not going forward; instead GoTo GetM is actioned.

 

[mikerickson]

My code is does not have the GetM label.

What do you want to happen if the user cancels? Do you want the routine to end or do you want the InputBox to be repeated?

 

[Juggler_IN]

Okay, If ---

1. InputBox CANCEL then --- MsgBox "Cancel button clicked, Input Box will close.", , "Cancel was clicked." --- Exit Sub
2. InputBox EMPTY then --- go back to [M = Application.InputBox("enter 0 or 1", Default:=0, Type:=5)]

 

[mikerickson]

The code that I posted, with type:=5, does not allow the user to enter a blank.
This code will loop the InputBox until the user enters 0, 1 or cancels.

Dim M As Variant

Do
    M = Application.InputBox("enter 0 or 1", Default:=0, Type:=5)
    
    If TypeName(M) = "Boolean" Then
        MsgBox "Cancel button clicked, Input Box will close.", , "Cancel was clicked."
        Range("A1").Select
        Exit Sub
    ElseIf M <> 0 And M <> 1 Then
        MsgBox "enter 0 or 1 only"
    End If
Loop Until M = 0 Or M = 1
    
MsgBox "user entered " & M