Closing the InputBox when Cancel is selected

[Raylou]

Good Morning,

I hope you all are doing well.

I was hoping that someone can perhaps show me how do I add code to exit the sub when the cancel button is selected on the InputBox. I have tried I few option on the forum, but do not seem to get it working. I am very new to vba and perhaps are missing the obvious.

I am filtering a list of data populated in sheet3 to only show certain data entries based on date values. this is working fine, but when I hit cancel the code is still running as if I entered a "0" for the days.

Your help would be highly appreciated.

I thank you in advance.

Sub Filter()
Dim i As Integer
Dim UseEnt As String

Sheets("Sheet3").Activate

ActiveSheet.AutoFilterMode = False


On Error Resume Next

    i = InputBox("Enter the number of days for the search.")

  If i Like "" Then
      ActiveSheet.AutoFilterMode = False
    Exit Sub

  End If

    UseEnt = Date - i

    Range("A2").CurrentRegion.AutoFilter Field:=3, Criteria1:="<=" & UseEnt

    Application.CutCopyMode = False

End Sub

[buran]

One of these two:

Sub Filter()
    Dim UseEnt As String
    Dim strUserInput As String

    Sheets("Sheet3").Activate

    ActiveSheet.AutoFilterMode = False
    Application.CutCopyMode = False    'you don't need this, unless you have performed copy paste somewhere else in the code

    strUserInput = InputBox("Enter the number of days for the search.")

    Select Case strUserInput
    Case vbNullString
        Exit Sub
    Case Else
        If IsNumber(strUserInput) Then
            UseEnt = Date - CInt(strUserInput)
            Range("A2").CurrentRegion.AutoFilter Field:=3, Criteria1:="<=" & UseEnt
        End If
    End Select

End Sub

OR

Sub Filter()
    Dim UseEnt As String
    Dim strUserInput As String

    Sheets("Sheet3").Activate

    ActiveSheet.AutoFilterMode = False
    Application.CutCopyMode = False    'you don't need this, unless you have performed copy paste somewhere else in the code

    strUserInput = InputBox("Enter the number of days for the search.")

    If strUserInput <> vbNullString Then

        If IsNumber(strUserInput) Then
            UseEnt = Date - CInt(strUserInput)
            Range("A2").CurrentRegion.AutoFilter Field:=3, Criteria1:="<=" & UseEnt
        End If

    End If

End Sub

[Raylou]

Hi there, thank for the prompt response.

IsNumber - what should that be defined as?

Thanks!

Excel.png

[buran]

Sorry. It should be IsNumeric.

There is VBA.IsNumeric which I wanted to use
and WorksheetFunction.IsNumber which is the same as =IsNumber() used on worksheet.

Just replace IsNumber with IsNumeric

[Raylou]

Hi there,

Thanks bith of your methods run the code perfectly, excpet that I still have the same problem. If I add for example 0 in my search, the filtered data appears perfectly in my ListBox, but if I hit "cancel" it list all the entries in my ListBox from my worksheet. Sound like not something easy to fix hehe. Below is a few screen dumps of what I means. Excel1.pngExcel2.png

[buran]

aha, I get it - you want to keep the current applied filter if Cancel click

Sub Filter()
    Dim UseEnt As String
    Dim strUserInput As String

'here you activate sheet3
    Sheets("Sheet3").Activate

'here you clear the blinking border that indicates copy command
    Application.CutCopyMode = False    'you don't need this, unless you have performed copy paste somewhere else in the code

'Here you get the user input. Store the user input in variable of type string
    strUserInput = InputBox("Enter the number of days for the search.")

'Check user input
    Select Case strUserInput
    Case vbNullString 'User click Cancel->Exit sub. Note that filter was already removed and all data visible
        Exit Sub
    Case Else 'User enter something, not Cancel
        If IsNumber(strUserInput) Then 'Check that user actually enter number, not text for example

'here you remove the autofilter mode. Now you are data are not filtered. all data visible
    ActiveSheet.AutoFilterMode = False

            UseEnt = Date - CInt(strUserInput) 'Find date which is some days before current date. If user enter 0 it's the current date (0 days offset)
            Range("A2").CurrentRegion.AutoFilter Field:=3, Criteria1:="<=" & UseEnt  'Apply filter. Displasy all rows for which date <= UseEnt. If UseEnt= current  date-> shows all dates <=today
        End If
    End Select

End Sub

[buran]

OK, let me explain the code step by step, because maybe I don't get right what you want. From your initial post my understanding was that you want to properly handle the cancel click.

Sub Filter()
    Dim UseEnt As String
    Dim strUserInput As String

'here you activate sheet3
    Sheets("Sheet3").Activate

'here you remove the autofilter mode. Now you are data are not filtered. all data visible
    ActiveSheet.AutoFilterMode = False

'here you clear the blinking border that indicates copy command
    Application.CutCopyMode = False    'you don't need this, unless you have performed copy paste somewhere else in the code

'Here you get the user input. Store the user input in variable of type string
    strUserInput = InputBox("Enter the number of days for the search.")

'Check user input
    Select Case strUserInput
    Case vbNullString 'User click Cancel->Exit sub. Note that filter was already removed and all data visible
        Exit Sub
    Case Else 'User enter something, not Cancel
        If IsNumber(strUserInput) Then 'Check that user actually enter number, not text for example
            UseEnt = Date - CInt(strUserInput) 'Find date which is some days before current date. If user enter 0 it's the current date (0 days offset)
            Range("A2").CurrentRegion.AutoFilter Field:=3, Criteria1:="<=" & UseEnt  'Apply filter. Displasy all rows for which date <= UseEnt. If UseEnt= current  date-> shows all dates <=today
        End If
    End Select

End Sub

[Raylou]

I think perhaps I am explaining like its already weekend or something hehe. Need a cold one.

I think where I am going wrong is that the whole filtering and everything is already happening and populating.

Basically all I want is, before anything happens, if I select the cancel button, my Listbox should remain empty, no entries. It should only populate information based on the number of days entered and if I enter a value and hit the "ok" button.

I hope I make more sense .

[buran]

OK, here in this code you don't populate any listbox. You just filter some data on the worksheet. The code that populates (some) listbox is somewhere else in your code. It's also possible that your listbox is linked to sourcerange on the sheet... In any case - from this part of the code I cannot see what is going on with your listbox.
Please provide full code. sample workbook, free of any sensitive data will be of great help

[Raylou]

Happiness!!!! Thanks buddy!!!!

You asked the right questions and all I had to do was to insert your code before the the code related to my listbox that you were referring to above.

I made the mistake in putting the code in a module and then call it in when I run a code from another command button.

Thanks a million, much appreciated!!!!