The "/" starts appearing in a cell - VBA

DECOVIOTI

New Member
Joined
Dec 11, 2020
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
Hello

The user made a last request, and could you please help me? When the user types the "Date From" and "Date to", the "/" starts appearing as long as the user types I am trying via the code below

VBA Code:
Sub TableFilt1()

Dim ToDate As Date, FrDate As Date

With Sheet6
    LastRow = .Range("E99999").End(xlUp).Row
    
    If .Range("M5").Value > .Range("N5").Value Then
         MsgBox "Invalid Date Range.""Date to"" must be greater than ""Date from"""
         Call ClearFilt
         .Range("A1").Select
    Exit Sub

    End If
    
    If .Range("M5").Value = "From Date" Then FrDate = "01/05/2018" Else: FrDate = .Range("M5").Value
    If .Range("N5").Value = "To Date" Then ToDate = "01/04/2021" Else: ToDate = .Range("N5").Value
    

    With .Range("E6:AU" & LastRow)
        .AutoFilter Field:=9, _
         Criteria1:=">=" & Left(FrDate, 2), & "/" & Mid(FrDate, 3, 2) & "/" & Right(FrDate, 2), _
         Operator:=xlAnd, _
         Criteria2:="<=" & Left(ToDate, 2), & "/" & Mid(ToDate, 3, 2) & "/" & Right(ToDate, 2)
    End With
End With
End Sub
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
I suggest you inhibit changing the filter by just putting a test to see if m5 or N5 is the active cell:
try this:
VBA Code:
ttst = ActiveCell.Address
If ttst <> "$M$5" And ttst <> "$N$5" Then
If .Range("M5").Value = "From Date" Then FrDate = "01/05/2018" Else: FrDate = .Range("M5").Value
    If .Range("N5").Value = "To Date" Then ToDate = "01/04/2021" Else: ToDate = .Range("N5").Value
  

    With .Range("E6:AU" & LastRow)
        .AutoFilter Field:=9, _
         Criteria1:=">=" & Left(FrDate, 2), & "/" & Mid(FrDate, 3, 2) & "/" & Right(FrDate, 2), _
         Operator:=xlAnd, _
         Criteria2:="<=" & Left(ToDate, 2), & "/" & Mid(ToDate, 3, 2) & "/" & Right(ToDate, 2)
    End With
End If
 
Upvote 0
Hello @
I suggest you inhibit changing the filter by just putting a test to see if m5 or N5 is the active cell:
try this:
VBA Code:
ttst = ActiveCell.Address
If ttst <> "$M$5" And ttst <> "$N$5" Then
If .Range("M5").Value = "From Date" Then FrDate = "01/05/2018" Else: FrDate = .Range("M5").Value
    If .Range("N5").Value = "To Date" Then ToDate = "01/04/2021" Else: ToDate = .Range("N5").Value
 

    With .Range("E6:AU" & LastRow)
        .AutoFilter Field:=9, _
         Criteria1:=">=" & Left(FrDate, 2), & "/" & Mid(FrDate, 3, 2) & "/" & Right(FrDate, 2), _
         Operator:=xlAnd, _
         Criteria2:="<=" & Left(ToDate, 2), & "/" & Mid(ToDate, 3, 2) & "/" & Right(ToDate, 2)
    End With
End If
Hello, offthelip ...thanks for the suggestion, but it did not work. i tried the code below

VBA Code:
Sub TableFilt1()
    '
    Dim ToDate                As Date, FrDate As Date
    
    With Sheet6
    LastRow = .Range("E99999").End(xlUp).Row
    
    ttst = ActiveCell.Address
    If ttst <> "$M$5" And ttst <> "$N$5" Then
        If .Range("M5").Value = "From Date" Then FrDate = "01/05/2018" Else: FrDate = .Range("M5").Value
        If .Range("N5").Value = "To Date" Then ToDate = "01/04/2021" Else: ToDate = .Range("N5").Value
        
        With .Range("E6:AU" & LastRow)
            .AutoFilter Field:=9, _
                        Criteria1:=">=" & Left(FrDate, 2) & "/" & Mid(FrDate, 3, 2) & "/" & Right(FrDate, 2), _
                        Operator:=xlAnd, _
                        Criteria2:="<=" & Left(ToDate, 2) & "/" & Mid(ToDate, 3, 2) & "/" & Right(ToDate, 2)
        End With
    End If
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,722
Members
448,294
Latest member
jmjmjmjmjmjm

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
Back
Top