Inputbox (Dim as string, can be blank) on Cancel

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
156
I've tried looking this up, but can't find a comparable problem with cancelling at an inputbox.

I've got an inputbox that is looking for a text entry, but the odd one out here is that it can left blank at the entry point (if they don't want to filter a list). My problem is trying to get the macro to cancel out if they hit Cancel at this point. I haven't been able to figure out how to make it work.

Here's what I've got (just giving the portion of the macro with the error). Right now, you'll see that I've got the lines commented out, as without them in there, the macro works fine...

Code:
'TextFilter for Client Name
    Dim ClientFilter As String
    ClientFilter = InputBox("Type in text you want to use to filter Client/Contract search:", "Client Name Text Filter")
    'If ClientFilter = vbCancel Then
    '    MsgBox "Macro is cancelled.  The current Client Contract selection remains as is."
    '    Exit Sub
    'End If

I know that it's probably and easy solution, but my brain just won't wrap around that.

Thanks in advance for your help!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,603
Office Version
  1. 365
Platform
  1. Windows
Use Application.InputBox instead. Like this

VBA Code:
Dim ClientFilter As Variant
ClientFilter = Application.InputBox("Type in text you want to use to filter Client/Contract search:", "Client Name Text Filter")
If ClientFilter = False Then
  MsgBox "Cancelled"
Else
  'Your other code goes here
End If
 
Solution

VBasic2008

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
Your setup is not quite clear, but the following might help.

VBA Code:
Option Explicit

Sub test()
    
    Dim ClientFilter As String
    ClientFilter = "Peter"
    Dim testClientFilter As String
    On Error Resume Next
    testClientFilter = InputBox("Type in text you want to use to filter Client/Contract search:", "Client Name Text Filter")
    On Error GoTo 0
    If testClientFilter = "" Then
        MsgBox "Macro is cancelled.  The current Client Contract selection remains as is."
    Else
        ClientFilter = testClientFilter
    End If
    Debug.Print ClientFilter

End Sub
 

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
156
Peter_SSs - your correction worked perfectly, with very little change. Thank you!

VBasic2008 - since I already tried Peter's and it worked, I'm not going to use yours, but I appreciate your quick assistance. :)
 

VBasic2008

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
When using InputBox you cannot easily (I saw a complicated solution once) find out if the user canceled or just left the box empty. The following shows how easy it is with Application.InputBox:

VBA Code:
Sub test2()
  
    Dim ClientFilter As String
    ClientFilter = "Peter"
    Dim testClientFilter As Variant
    testClientFilter = Application.InputBox("Type in text you want to use to filter Client/Contract search:", "Client Name Text Filter")
    Select Case testClientFilter
        Case False
            MsgBox "User pressed Cancel."
        Case ""
            MsgBox "User left the box empty and pressed OK."
        Case Else
            ClientFilter = testClientFilter
    End Select
    Debug.Print ClientFilter

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,381
Messages
5,641,805
Members
417,239
Latest member
AymericA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top