Auto update all dates in a column to todays date on workbook open

Hi Guys,

I'm a complete noob when it comes to VBA programming in excel and was wondering if someone could help me with a problem I'm having.

I have a task/issue tracking sheet that I use to manage my day to day workload. The sheet has a task due date column. Sometimes for whatever reason some dates pass before the item is complete and I would like it if these past dates auto updated (on workbook open) to todays date.

I've given it a go but have failed so far.

Can anyone help?

Cheers

Sam

Hi, thanks for your help.

Unfortunately it didn't work. Is it because I placed the code in the module for a sheet within the workbook not in "ThisWorkbook"? I should have said but I don't want the code to affect the entire workbook, just the issue/task tracking sheet within this workbook.

Cheers

Sam

Just tried your suggestion pjwhitfield.

Capture.PNG

Not sure why the third line down has a problem??

Originally Posted by pjwhitfield

Reference the range and sheet you want to use

Please Login or Register  to view this content.

Placed this code in "ThisWorkbook". Changed "sheet1" and "A:A" to the the right sheet and column. Saved, closed and reopened the workbook. This error message appeared "Run-time error '9': Subscript out of range".

any idea?

Originally Posted by pjwhitfield

Post your version of the code please

Private Sub Workbook_Open()
Dim rng As Range
Set rng = Worksheets("Sheet10").Range("B:B")
For Each cell In rng
If ((cell.Value < Date) And (IsDate(cell.Value))) Then
cell.Value = Date
End If
Next cell
End Sub

Originally Posted by pjwhitfield

If I paste that into a new workbook and create a sheet10 with some data in column B it works fine.

The only code in there which would cause a Subscript out of range error is the setting of the range which at first glance looks fine.

Check your name of Sheet10, make sure it hasnt somehow got a space in it or something else. Try renaming that sheet (unless theres other code in your workbook that depends on it) and then changing the reference in this code to see if that makes a difference.

Yep, you were right. The sheet reference was wrong. It works now!

I've tried adding to the code as I would like it if only the cells on "open" tasks update with the current date. Immediately to the left of each dated cell there is a cell containing either "Open" or "Closed". So the dated cell (column B) should only update if the Open/Closed cell (column A) contains "Open". Here is my attempt at the code:

Private Sub Workbook_Open()
Dim rng As Range
Set rng = Worksheets("Task and Issue Tracking").Range("B:B")
For Each cell In rng
If ((cell.Value < Date) And (IsDate(cell.Value))) And (ActiveCell.Offset(-1, 0).Value = "Open") Then
cell.Value = Date
End If
Next cell
End Sub

Unfortunately this doesn't work. Any suggestions on what I'm doing wrong would be great!

Cheers, Sam

Originally Posted by smorri25

Yep, you were right. The sheet reference was wrong. It works now!

I've tried adding to the code as I would like it if only the cells on "open" tasks update with the current date. Immediately to the left of each dated cell there is a cell containing either "Open" or "Closed". So the dated cell (column B) should only update if the Open/Closed cell (column A) contains "Open". Here is my attempt at the code:

Private Sub Workbook_Open()
Dim rng As Range
Set rng = Worksheets("Task and Issue Tracking").Range("B:B")
For Each cell In rng
If ((cell.Value < Date) And (IsDate(cell.Value))) And (ActiveCell.Offset(-1, 0).Value = "Open") Then
cell.Value = Date
End If
Next cell
End Sub

Unfortunately this doesn't work. Any suggestions on what I'm doing wrong would be great!

Cheers, Sam

Any suggestions guys?