VBA script doesn't update todays date

[Fredrik1987]

I want to calculate the number of days until deadline.
Column G15:G1000 is set as the deadline date.

I have a similiar code in a worksheet_change script, and it works fine.
Also, I'm relatively new to the whole "VBA experience", and haven't really used Workbook_Open() before.

Sub Workbook_Open(ByVal Target As Range)

If Not Application.Intersect(Target, Range("H15:H1000")) Is Nothing Then
    Application.EnableEvents = False
   
    If Target.Value = "Active" And Target.Offset(, -3).Value = "Work" Then _

        Target.Offset(, -2).Value = DateDiff("d", Date, Target.Offset(, -1)) & " Days remaining"

End If
End If

End Sub

To summarize, the part of the script in Worksheet_Change works fine, I set the case to active and the number of days until deadline appears (this is just a part of a larger script). When I open Excel the next day, nothing happens, the number of days until deadline is the same.

 

[pgc01]

Hi Fredrik
Welcome to the board

Sub Workbook_Open(ByVal Target As Range)

What it this Target parameter?
As far as I know Workbook_Open() does not have parameters.

If Not Application.Intersect(Target, Range("H15:H1000")) Is Nothing Then

This Range("H15:H1000") refers to a range in whatever worksheet is active.
Do you know which worksheet is active?

Can't understand. Please explain.

 

[Fredrik1987]

Hi!

I'm relatively new to VBA.
I already have a script in another sub, which is a Worksheet_Change(ByVal Targer As Range) sub.
I have simply copy-pasted a part of the script, and created a new sub. Something like this:

Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range("H15:H1000")) Is Nothing Then
    Application.EnableEvents = False
   
    If Target.Value = "Komplett" And Target.Offset(, -3).Value = "JA" Then _

        Target.Offset(, -2).Value = DateDiff("d", Date, Target.Offset(, -1)) & " Dager igjen"

    End If
End If
End Sub

Sub Workbook_Open(ByVal Target As Range)

If Not Application.Intersect(Target, Range("H15:H1000")) Is Nothing Then
    Application.EnableEvents = False
   
    If Target.Value = "Komplett" And Target.Offset(, -3).Value = "JA" Then _

        Target.Offset(, -2).Value = DateDiff("d", Date, Target.Offset(, -1)) & " Dager igjen"

    End If
End If

End Sub

I just assumed Workbook_Open worked the same way as Worksheet_change, where you define and set a specific range.
As for active sheets, I'm unsure what you mean. I have all my information in one Sheet ("Sheet1"), and this is the one I work in.

Perhaps I should explain a bit more about the purpose of the script:

I work with several different papers, who all have different deadlines.
So in my sub Workbook_Change, the deadline appears if I set the case as "Active", in column H.
However, if column E (Offset -3) is not set as "Work" i don't have a deadline and don't need to calculate it.
This part works just fine.

BUT, this sub doesn't update the time until deadline, unless I update the value in column H manually (which, for several reasons, I can't do). So if I set the case as "Active" I get the difference between todays date and the final date the customer want the product. Naturally, I want this date to change as I get closer to the deadline, but this doesn't happen.

So I tried creating a Workbook_Open() sub, so that the number of days remaining updates each time I open the workbook. So far this isn't happening, it only shows the same number of days remaining as it did yesterday.

 

[pgc01]

Hi

1 - First a practical note:

To insert the event procedures with the correct syntax (including all the parameters) you can use the dropdowns above the editing area in the vbe.

Ex., for a workbook event.

a - select the ThisWorkbook module
b - you'll see above the editing pane 2 dropdowns. On the left dropdown you'll have "(General)". Click on it and select "Workbook"
c - If you clik now on the dropdown to the right, you'll get a list with all the workbook events. You just have to click on 1 of them and vbe will insert automatically the beginning and end of the event procedure with the declaration of all the parameters.

2 - the Workbook_Open() event has no parameters.

I did not understand exactly what you need, but the rule is that you have to specify clearly what you want to do. What ranges in what worksheets, etc.

This is an example:

Private Sub Workbook_Open()
Worksheets("Sheet1").Range("A1").Value = Time
End Sub

This is a simple example but illustrates the idea. It is perfectly clear to excel what has to be done. To write the current time value in the cell A1 of the worksheet "Sheet1".

Hope this helps.

Post back if you have doubts.

 

[Fredrik1987]

Yes, As I read my post again, I understand why it was nearly impossible to understand my question.
I re-uloaded the same question with a simple example to try to show what my intentions are

Link to updated question:
http://www.mrexcel.com/forum/excel-...cations-script-doesnt-update-todays-date.html