PDA

View Full Version : Macro / VBA help for a complete newbie, please? Auto-delete data/helper cell



M_Neal
07-24-2015, 02:27 AM
Hello all -

I've worked with Excel for a while but have never dipped my toe into programming. I was able to find some examples that I modified to accomplish what I've listed below, but I don't really know how to do anything else.

I am creating a spreadsheet to track/plot employee vacations. Each employee has a worksheet that tracks accrued vacation and has start/end dates. There is a master listing that shows start/end dates for each vacation and a chart of requested vacation, chart range is updated automatically based on min / max values for vacation dates.

What I have:
Code that automatically updates the chart based on requested vacation when the master list worksheet is made active.

Private Sub worksheet_Activate() Dim objCht As ChartObject
For Each objCht In ActiveSheet.ChartObjects
With objCht.Chart
' Value (X) Axis
With .Axes(xlValue)
.MaximumScale = ActiveSheet.Range("D24").Value
.MinimumScale = ActiveSheet.Range("D23").Value - 3

End With
End With
Next objCht
End Sub

I also have a macro in each employee sheet that automatically deletes old vacation information when the employee worksheet is made active.
Note: R14 is a hidden helper cell that returns "TRUE" if vacation ended more than 3 days ago. C1 through C5 is vacation request data.


Private Sub worksheet_Activate()
Dim target As String
target = Range("R14")
If target = "True" Then
Range("C1:C5").ClearContents
On Error Resume Next
End If
End Sub

What I want to do:
Automatically clear vacation data from any employee sheet (only employee sheets, it needs to skip the master list sheet) where the end of vacation date (cell C5) is more than three days old (Today - 3 days). I've googled and tried at least a dozen different methods but I can't seem to make it work. I was thinking that a macro that activated each employee sheet in turn (thus triggering the clear macro above) might work, but it didn't. Not sure why it didn't work or how to make it work.

Any help would be *GREATLY* appreciated!!!