joey2751
01-07-2010, 12:34 PM
Hello,
I currently have code that enables me to insert a date in an imput box. I have created a calendar in a UserForm. I would like to be able to select a date from the calendar as opposed to using an input box. How do I incoporate the calendar into the myDate instead of using an input box?
Here is my current code:
Sub StaleMacro()
Const cColumnD = 4 'COLUMN=D
Dim myRow As Integer
Dim ws As Worksheet
Dim vCellValue As Variant
Dim myRowStr As String
Dim myDate As Date
myDate = InputBox("Date Format ##/##/####", "Input Last Friday")
'enumerate worksheets collection
For Each ws In Worksheets
'select worksheet
Sheets(ws.Name).Select
'traverse cells, from last used cell to first one
For myRow = ws.UsedRange.Rows.Count To 1 Step -1
'get cell value
vCellValue = ws.Cells(myRow, cColumnD)
'is value a date?
If IsDate(vCellValue) Then
'compare date, delete row
If vCellValue >= myDate Then ws.Rows(myRow).Delete
End If
Next myRow
Next ws
Sheets("StaleOutput").Select
Columns("N:R").Select
Selection.Delete Shift:=xlToLeft
Range("J2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Call Stale2
End Sub
I currently have code that enables me to insert a date in an imput box. I have created a calendar in a UserForm. I would like to be able to select a date from the calendar as opposed to using an input box. How do I incoporate the calendar into the myDate instead of using an input box?
Here is my current code:
Sub StaleMacro()
Const cColumnD = 4 'COLUMN=D
Dim myRow As Integer
Dim ws As Worksheet
Dim vCellValue As Variant
Dim myRowStr As String
Dim myDate As Date
myDate = InputBox("Date Format ##/##/####", "Input Last Friday")
'enumerate worksheets collection
For Each ws In Worksheets
'select worksheet
Sheets(ws.Name).Select
'traverse cells, from last used cell to first one
For myRow = ws.UsedRange.Rows.Count To 1 Step -1
'get cell value
vCellValue = ws.Cells(myRow, cColumnD)
'is value a date?
If IsDate(vCellValue) Then
'compare date, delete row
If vCellValue >= myDate Then ws.Rows(myRow).Delete
End If
Next myRow
Next ws
Sheets("StaleOutput").Select
Columns("N:R").Select
Selection.Delete Shift:=xlToLeft
Range("J2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Call Stale2
End Sub