Changing Date in Input Box

[helpexcel]

I'm using the code below and today's date is pre-populated in the Input box. Is there a way to make it today() - 7 days?

thanks!

[I]Sub DeleteFromDate()
[/I]Application.ScreenUpdating = FalseApplication.DisplayAlerts = False
Dim LR As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
DateR = Application.InputBox("Enter based on date to delete", TitleMsg, FormatDateTime(Date, vbShortDate), Type:=1)
Cells.AutoFilter Field:=2, Criteria1:="<=" & DateR
ALR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
If ALR > 2 Then
    Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).Select
    Range("A2:A" & LR).Delete
    Range("A1").Activate
End If
Cells.AutoFilter
MsgBox "Finished deleting rows"
Application.DisplayAlerts = True
Application.ScreenUpdating = True [I]End Sub[/I]

 

[DanteAmor]

Try:

DateR = Application.InputBox("Enter based on date to delete", TitleMsg, FormatDateTime([COLOR=#ff0000]Date - 7[/COLOR], vbShortDate), Type:=1)

 

[helpexcel]

works great thanks!

Any idea on how to make the cancel button work on the inputbox?

 

[dmt32]

Any idea on how to make the cancel button work on the inputbox?

Hi,
When you select the Cancel button using InputBox method, it returns False.

DateR = Application.InputBox("Enter based on date to delete", TitleMsg, FormatDateTime(Date, vbShortDate), Type:=1)
'cancel pressed
If VarType(DateR) = vbBoolean Then Exit Sub

Assuming your variable is declared as variant data type then
Add the line shown in red & see if does what you want

Dave

 

[DanteAmor]

works great thanks!

Any idea on how to make the cancel button work on the inputbox?

Try this

Sub DeleteFromDate()
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Dim LR As Long
  [COLOR=#0000ff]Dim DateR As Variant[/COLOR]
  Dim TitleMsg As String, ALR As Long
  LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
  DateR = Application.InputBox("Enter based on date to delete", TitleMsg, FormatDateTime(Date, vbShortDate), Type:=1)
[COLOR=#0000ff]  If DateR = "" Or DateR = False Then[/COLOR]
[COLOR=#0000ff]    MsgBox "Operation cancelled"[/COLOR]
[COLOR=#0000ff]    Exit Sub[/COLOR]
[COLOR=#0000ff]  End If[/COLOR]
  Cells.AutoFilter Field:=2, Criteria1:="<=" & DateR
  ALR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
  If ALR > 2 Then
      Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).Select
      Range("A2:A" & LR).Delete
      Range("A1").Activate
  End If
  Cells.AutoFilter
  MsgBox "Finished deleting rows"
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
End Sub