Hi Everyone.
I made this macro for fun, its based on a post last Sept.30.(Remove 1 from a
total after every day) answered by Ron Rosenfeld with a formula
(Networkdays).The post was on Answers.Microsoft.com
I do this for learning.I'm just at the learning stage.
I have a Date in A1 and a number in C1 that goes down by one everyday.
Now, my problem is, I want it just for Workdays..nor everydays.
I'm lost with this, can anyone help.

Option Explicit
Private Sub Workbook_Open()
Dim stdate As Date
Dim tday As Date
Dim ans As Integer
On Error GoTo Finish:
stdate = ActiveSheet.Range("A1")'Start Date
tday = Now()
ans = tday - stdate
If tday stdate Then
Range("F1") = Range("C1") + 1 - ans
If Range("F1").Value <= 0 Then
Range("F1").Interior.ColorIndex = 3
MsgBox "Your Time has expired"
Else
Range("F1").Interior.ColorIndex = 0
End If
End If
Finish:
End Sub

Regards
Cimjet

You will want to incorporate NETWORKDAYS (look in help)
in vba use
MsgBox Application.Run("ATPVBAEN.XLA!networkdays", [a7], [a8])

or if you, in the VBE ,do toolsrefrencesatpvbaen then
MsgBox networkdays([a7], [a8])
=========================
On Oct 2, 10:25*am, "Cimjet" wrote:
Hi Everyone.
I made this macro for fun, its based on a post last Sept.30.(Remove 1 from a
total after every day) answered by Ron Rosenfeld with a formula
(Networkdays).The post was on Answers.Microsoft.com
I do this for learning.I'm just at the learning stage.
I have a Date *in A1 and a number in C1 that goes down by one everyday.
Now, my problem is, I want it just for Workdays..nor everydays.
I'm lost with this, can anyone help.

Option Explicit
Private Sub Workbook_Open()
Dim stdate As Date
Dim tday As Date
Dim ans As Integer
* * * * On Error GoTo Finish:
stdate = ActiveSheet.Range("A1")'Start Date
* tday = Now()
* *ans = tday - stdate
* * If tday stdate Then
* * * * Range("F1") = Range("C1") + 1 - ans
* * If Range("F1").Value <= 0 Then
* * * * Range("F1").Interior.ColorIndex = 3
* * * * MsgBox "Your Time has expired"
Else
* * Range("F1").Interior.ColorIndex = 0
* * End If
* * End If
Finish:
End Sub

Regards
Cimjet

Hi Don
Thank you, it works fine.
I have a question for you, I reference atpvbaen.xla in my Project VBA to make it
work, is this because Networkdays is not a standard formula, it's part of the
Analisis Toolpak, I'm using XL03.
Thanks again
Cimjet
"Don Guillett" wrote in message
...
You will want to incorporate NETWORKDAYS (look in help)
in vba use
MsgBox Application.Run("ATPVBAEN.XLA!networkdays", [a7], [a8])

or if you, in the VBE ,do toolsrefrencesatpvbaen then
MsgBox networkdays([a7], [a8])
=========================
On Oct 2, 10:25 am, "Cimjet" wrote:
Hi Everyone.
I made this macro for fun, its based on a post last Sept.30.(Remove 1 from a
total after every day) answered by Ron Rosenfeld with a formula
(Networkdays).The post was on Answers.Microsoft.com
I do this for learning.I'm just at the learning stage.
I have a Date in A1 and a number in C1 that goes down by one everyday.
Now, my problem is, I want it just for Workdays..nor everydays.
I'm lost with this, can anyone help.

Option Explicit
Private Sub Workbook_Open()
Dim stdate As Date
Dim tday As Date
Dim ans As Integer
On Error GoTo Finish:
stdate = ActiveSheet.Range("A1")'Start Date
tday = Now()
ans = tday - stdate
If tday stdate Then
Range("F1") = Range("C1") + 1 - ans
If Range("F1").Value <= 0 Then
Range("F1").Interior.ColorIndex = 3
MsgBox "Your Time has expired"
Else
Range("F1").Interior.ColorIndex = 0
End If
End If
Finish:
End Sub

Regards
Cimjet

Typo: "Analysis Toolpak"
Just so you know, I tried help in VBA editor but got nothing , maybe I don't
know how to use it properly.
I usually highlight the word or words and press F1. Any comments on that.
Regards
Cimjet

"Cimjet" wrote in message
...
Hi Don
Thank you, it works fine.
I have a question for you, I reference atpvbaen.xla in my Project VBA to make
it work, is this because Networkdays is not a standard formula, it's part of
the Analysis Toolpak, I'm using XL03.
Thanks again
Cimjet
"Don Guillett" wrote in message
...
You will want to incorporate NETWORKDAYS (look in help)
in vba use
MsgBox Application.Run("ATPVBAEN.XLA!networkdays", [a7], [a8])

or if you, in the VBE ,do toolsrefrencesatpvbaen then
MsgBox networkdays([a7], [a8])
=========================
On Oct 2, 10:25 am, "Cimjet" wrote:
Hi Everyone.
I made this macro for fun, its based on a post last Sept.30.(Remove 1 from a
total after every day) answered by Ron Rosenfeld with a formula
(Networkdays).The post was on Answers.Microsoft.com
I do this for learning.I'm just at the learning stage.
I have a Date in A1 and a number in C1 that goes down by one everyday.
Now, my problem is, I want it just for Workdays..nor everydays.
I'm lost with this, can anyone help.

Option Explicit
Private Sub Workbook_Open()
Dim stdate As Date
Dim tday As Date
Dim ans As Integer
On Error GoTo Finish:
stdate = ActiveSheet.Range("A1")'Start Date
tday = Now()
ans = tday - stdate
If tday stdate Then
Range("F1") = Range("C1") + 1 - ans
If Range("F1").Value <= 0 Then
Range("F1").Interior.ColorIndex = 3
MsgBox "Your Time has expired"
Else
Range("F1").Interior.ColorIndex = 0
End If
End If
Finish:
End Sub

Regards
Cimjet