Default Inputbox Value Date

[3nduranc3]

Hello!

I am very happy to post my first problem here for the expert community.

I made a selection and coloring macro, the filter is based on the inputbox that pops up. What I would like to do here is, that when the box pops up it would have a written date in it, which is the today always. And the filter should select all which is 2 weeks later than current date. Could you help me please?

Sub kethet()
Dim datum As Long
Dim most As String
LRow = Range("E" & Rows.Count).End(xlUp).Row
most = Application.InputBox("Meddig nézzük? ""(éééé.hh.nn)""")
 
datum = CLng(DateValue(most))
Selection.AutoFilter
ActiveSheet.Range("$A$1:$V$" & LRow).AutoFilter Field:=12, Criteria1:=">=" & datum
Range("A2:V" & LRow).SpecialCells(xlCellTypeVisible).Select
With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
       .PatternTintAndShade = 0
End With
Selection.AutoFilter
End Sub

 

[dmt32]

Hi welcome to board.

se if these changes to your code (shown in red) help. I have added some code to test if cancel button is pressed & for valid date values. The Default date format is set in Format dd/mm/yyyy but you can change as required.

Hope helpful

Dave.

Sub kethet()
    Dim datum As Long
    Dim most As Variant
    LRow = Range("E" & Rows.Count).End(xlUp).Row
DateIn:
    most = Application.InputBox("Meddig nézzük? ""(éééé.hh.nn)""", Default:=Format(Date, "dd/mm/yyyy"))
    If most = False Or Len(most) = 0 Then
        'cancel pressed or
        'no input
        Exit Sub
    ElseIf IsDate(most) Then
        datum = CLng(DateValue(most))
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$V$" & LRow).AutoFilter Field:=12, _
        Criteria1:=">=" & datum
        
        Range("A2:V" & LRow).SpecialCells(xlCellTypeVisible).Select
        
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.149998474074526
            .PatternTintAndShade = 0
        End With
        Selection.AutoFilter
    Else
        MsgBox "Not A Valid Date", 16, "Error"
        GoTo DateIn
    End If
End Sub

 

[3nduranc3]

Thanks dmt!

Now it is very elegant and works fine, is there any way I can put in the + 2 weeks after date? I mean here:

ActiveSheet.Range("$A$1:$V$" & LRow).AutoFilter Field:=12, _ Criteria1:=">=" & datum + 2 weeks???</PRE>
How should I put it in, I presume +14 wont work and wont change month, etc.?

 

[dmt32]

after this line

ElseIf IsDate(most) Then

try adding
datum = datum + 14

Dave

 

[dmt32]

sorry, distracted playing with my grandson.
should have been:


ElseIf IsDate(most) Then
most = CDate(most) + 14

Dave

 

[3nduranc3]

After 20 mins of trying I realized I'm trying in one of my other macros, doh. :D Well thanks Dave, it works awsome. You helped me to take another step in excelling Excel! Cheers!