Excel InputBox

[Biz]

Dear All,
I have boolean input box as I want code to only allow 0,1, True, False input only.
If nothing entered then Msgbox "Please try again" should appear.

Sub Tests()
     '// Vars
1    Dim blnAns As Boolean
      
      '// Boolean prompt, 1 = true, 0 = false
2    blnAns = Application.inputbox(Prompt:="Do you want filter a particular item?" & vbCr & _
      "1 = True, 0 = False", Title:="Filter List", Type:=4)

   If StrPtr(blnAns) = 0 Then
   MsgBox ("Cancel pressed!")
    Exit Sub
   End If
      '~~> Check only 1 / True 0 / False as between entered
      If Application.Sum((blnAns >= 0 And blnAns <= 1), (blnAns = "True" Or blnAns = "False")) > 0 Then
              MsgBox "You have responded within range allowed :" & blnAns, vbInformation
          Else
              MsgBox "Please try again", vbCritical
              Exit Sub
          End If
End Sub

Your help would be greatly appreciated.

Biz

 

[portews]

Well... I learned several things today. Application.Inputbox is different from Inputbox, and, if you set the variable for it as a variant, the return from the former will be a string if it's something and a boolean False if it's canceled.
So, try this:

Sub inputbx()
Dim blnAns As Boolean
Dim x As Variant

x = Application.InputBox(Prompt:="Do you want filter a particular item?" & vbCr & _
      "1 = True, 0 = False", Title:="Filter List")

'take care of cancel
If (VarType(x) = vbBoolean) And (x = False) Then End

'I love Select Case
Select Case x
'Gave you some bonus correct answers... they're FEATURES!
Case 0, "No", "no", "False", "false"
     blnAns = False
Case 1, "Yes", "yes", "True", "true"
    blnAns = True
Case Else
    MsgBox "Please try again", vbCritical
    Exit Sub
End Select

MsgBox "You have responded within range allowed :" & blnAns, vbInformation

End Sub

 

[johnnyL]

This is what I came up with:

Sub Tests()
'// Vars
'
1    Dim blnAns As Variant
'
'// Boolean prompt, 1 = true, 0 = false
'
2    blnAns = Application.InputBox(Prompt:="Do you want filter a particular item?" & vbCr & _
            "1 = True, 0 = False", Title:="Filter List", Type:=6)
'
    If blnAns = False Or blnAns = 0 Then
        MsgBox ("Cancel was pressed or zero was entered or 'False' was entered!")
        Exit Sub
    ElseIf blnAns = vbNullString Then
        MsgBox "Please try again", vbCritical
        Exit Sub
    Else
        MsgBox "You have responded within range allowed :" & blnAns, vbInformation
    End If
End Sub

 

[Biz]

Well... I learned several things today. Application.Inputbox is different from Inputbox, and, if you set the variable for it as a variant, the return from the former will be a string if it's something and a boolean False if it's canceled.
So, try this:

Sub inputbx()
Dim blnAns As Boolean
Dim x As Variant

x = Application.InputBox(Prompt:="Do you want filter a particular item?" & vbCr & _
      "1 = True, 0 = False", Title:="Filter List")

'take care of cancel
If (VarType(x) = vbBoolean) And (x = False) Then End

'I love Select Case
Select Case x
'Gave you some bonus correct answers... they're FEATURES!
Case 0, "No", "no", "False", "false"
     blnAns = False
Case 1, "Yes", "yes", "True", "true"
    blnAns = True
Case Else
    MsgBox "Please try again", vbCritical
    Exit Sub
End Select

MsgBox "You have responded within range allowed :" & blnAns, vbInformation

End Sub

Hi,

Thank you for your reply.

I have changed code with proper case and it eliminates case problems.
Revised code

Sub inputbx2()
    Dim blnAns As Boolean
    Dim x As Variant
    
    '~~> Get answer as Variant
    x = Application.inputbox(Prompt:="Do you want filter a particular item?" & vbCr & _
    "1 = True, 0 = False", Title:="Filter List")
    
    '~~> Check for cancel button clicked
    If (VarType(x) = vbBoolean) And (x = False) Then
        MsgBox "Sorry, but you cannot continue", vbCritical
        Exit Sub
    End If
    
    
    '~~> Case Statements with Proper to avoid case sensitivity
    Select Case Application.Proper(x)
        '~~> Case 0 stands for False or "No" if user types it.
        Case 0, "No", "False"
            blnAns = False
         '~~> Case 1 stands for True or and "Yes" if user types it.
        Case 1, "Yes", "True"
            blnAns = True
        Case Else
            MsgBox "Please try again", vbCritical
            Exit Sub
    End Select
    
    MsgBox "You have responded within range allowed :" & blnAns, vbInformation
    
End Sub

 

[Biz]

This is what I came up with:

Sub Tests()
'// Vars
'
1    Dim blnAns As Variant
'
'// Boolean prompt, 1 = true, 0 = false
'
2    blnAns = Application.InputBox(Prompt:="Do you want filter a particular item?" & vbCr & _
            "1 = True, 0 = False", Title:="Filter List", Type:=6)
'
    If blnAns = False Or blnAns = 0 Then
        MsgBox ("Cancel was pressed or zero was entered or 'False' was entered!")
        Exit Sub
    ElseIf blnAns = vbNullString Then
        MsgBox "Please try again", vbCritical
        Exit Sub
    Else
        MsgBox "You have responded within range allowed :" & blnAns, vbInformation
    End If
End Sub

The code doesn't work when I enter zero.

 

[johnnyL]

The code doesn't work when I enter zero.

It sure does. It is handled as a cancel.

 

[Biz]

It sure does. It is handled as a cancel.

It does handle cancel but if I enter number zero it failed. I have changed code to make it work.

Sub Tests()
'// Vars
'
1    Dim blnAns As Variant
'
'// Boolean prompt, 1 = true, 0 = false
'
2    blnAns = Application.inputbox(Prompt:="Do you want filter a particular item?" & vbCr & _
            "1 = True, 0 = False", Title:="Filter List", Type:=6)
'
    If (VarType(blnAns) = vbBoolean) And (blnAns = False) Or blnAns = vbNullString Then
    MsgBox "Sorry, but you cannot continue", vbCritical
    ElseIf blnAns = False And blnAns = 0 And blnAns = vbNullString Then
        MsgBox "Please try again", vbCritical
        Exit Sub
    Else
        MsgBox "You have responded within range allowed :" & blnAns, vbInformation
    End If
End Sub

Biz

 

[johnnyL]

If you say so.

Perhaps you can explain when

        MsgBox "Please try again", vbCritical

Would be executed in your code.

 

[Biz]

If you say so.

Perhaps you can explain when

        MsgBox "Please try again", vbCritical

Would be executed in your code.

When first input box arrives you don't enter anything and press either "Ok" or "Cancel" then executes MsgBox "Please try again", vbCritical.

 

[johnnyL]

Nope, try again!