Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

help type mismatch filter data by inputbox

0

hello  

i have  this  code  i try   add warning massege  when inputbox =""  if press ok it show the massage if press cancel  then exit sub  but it gives me error 

Sub filtdat()
Dim Lastrow As Integer
Dim Daterange As Date
Lastrow = Columns("A:A").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Daterange = InputBox("Please enter effective date (DD/MM/YYYY)")
If Daterange = "" Then MsgBox "YOU HAVE TO FILL THE DATE"
 cancel = True
 If Daterange = cancel Then Exit Sub
If Daterange <> "" Then
Rows(1).AutoFilter
ActiveSheet.Range("$A$1:$F" & Lastrow).AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria1:=Format(Daterange, "DD/MM/YYYY")
        End If
End Sub

Answer
Discuss

Answers

0
Selected Answer

To properly program an InputBox, as you are attempting to do, is surprisingly complicated. Perhaps the reason is that it hasn't been changed much since the turn of the century. Yet, it isn't difficult but one has to be precise.

Key is to understand that the InputBox function returns a variant of string type. This means that if you enter nothing it's a null string (a string of zero length but not zero). Otherwise, since it's a variant, it can be anything. A date is of particular difficulty because it's a string by nature but a number by Excel's use. CDate(DateRange) will instruct VBA to convert the "variant of string type" to a "variant of date type). After that you can treat it as a date.

Sub filtdat()

    Dim Lastrow     As Long
    Dim DateRange   As Variant

    Lastrow = Columns("A:A").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Do
        DateRange = InputBox("Please enter effective date (DD/MM/YYYY)")
        If DateRange = "" Then Exit Sub

        If IsDate(DateRange) Then
            DateRange = CDate(DateRange)
            Exit Do
        Else
            MsgBox "YOU HAVE TO FILL THE DATE"
        End If
    Loop

    With ActiveSheet
        If Not .AutoFilterMode Then .Rows(1).AutoFilter
        .Range("$A$1:$F" & Lastrow).AutoFilter Field:=1, _
                                               Operator:=xlFilterValues, _
                                               Criteria1:=Format(DateRange, "DD/MM/YYYY")
    End With
End Sub

Generally speaking, addressing the ActiveSheet is dangerous unless the code controls which sheet is active at any given moment. In the above context it seems safer to name the sheet, like , With Worksheets("MySheet")

Discuss

Discussion

awesome ! your updating    thanks  so  much 
leopard (rep: 88) Sep 11, '20 at 1:03 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login