Hi All...........

I have a workbook with Sheet1 protected on our network that is a WorkOrder
Form. It is revised regularly. Users go to the network and download this
file to their computer or laptop and fill it out and save it to another
directory under another name. I want to be sure they always use the latest
version of the file. So, to that end, I believe I would like to do the
folllowing, unless someone knows a better way.

1-I want a macro to automatically insert the =now() function in A1, AND
convert it over to a frozen value for that point in time.......this will
tell me exactly when they downloaded the file, especially for the notebook
guys. This would be a one-time function and work only when they first
download the filel.

2-Cell A2 normally gets a date inserted by the user. When they do this, I
would like to measure the difference between this date and the frozen date
in A1 and if it exceeds 30 days, to have a pop-up message requesting them to
"Please use the more current version of the form". No further data entry
could then be made to that file.

Is this approach feasible?...........or might something else fulfill my
needs better?

Any help would be appreciated,

Vaya con Dios,
Chuck, CABGx3

Hi
try the following code. Put this in the workbook module of your file
(not in a standard module):

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim wks As Worksheet
Dim rng As Range
Set wks = Me.Worksheets("Sheet1") 'change this if required
Set rng = wks.Range("A1")
With rng
If IsDate(.Value) And IsDate(.Offset(0, 1).Value) Then
If .Offset(0, 1).Value - .Value 30 Then
MsgBox "Saving not possible - old workbook"
Cancel = True
End If
End If
End With
End Sub


Private Sub Workbook_Open()
Dim wks As Worksheet
Dim rng As Range
Set wks = Me.Worksheets("Sheet1") 'change this if required
Set rng = wks.Range("A1")
With rng
If rng.Value = "" Then
.Value = Date
.NumberFormat = "MM-DD-YYYY"
ElseIf IsDate(.Offset(0, 1).Value) Then
If .Offset(0, 1).Value - .Value 30 Then
MsgBox "Old Workbook - Don't enter anything"
End If
End If
End With
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

"CLR" schrieb im Newsbeitrag
...
Hi All...........

I have a workbook with Sheet1 protected on our network that is a
WorkOrder
Form. It is revised regularly. Users go to the network and download
this
file to their computer or laptop and fill it out and save it to
another
directory under another name. I want to be sure they always use the
latest
version of the file. So, to that end, I believe I would like to do
the
folllowing, unless someone knows a better way.

1-I want a macro to automatically insert the =now() function in A1,
AND
convert it over to a frozen value for that point in time.......this
will
tell me exactly when they downloaded the file, especially for the
notebook
guys. This would be a one-time function and work only when they
first
download the filel.

2-Cell A2 normally gets a date inserted by the user. When they do
this, I
would like to measure the difference between this date and the frozen
date
in A1 and if it exceeds 30 days, to have a pop-up message requesting
them to
"Please use the more current version of the form". No further data
entry
could then be made to that file.

Is this approach feasible?...........or might something else fulfill
my
needs better?

Any help would be appreciated,

Vaya con Dios,
Chuck, CABGx3

Thank you ever so much Frank.............I'm on my way to work now and will
try it out today........with this code, I shall surely attain "Super Hero"
status at work <g..........

Thanks again,
Vaya con Dios,
Chuck, CABGx3






"Frank Kabel" wrote in message
...
Hi
try the following code. Put this in the workbook module of your file
(not in a standard module):

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim wks As Worksheet
Dim rng As Range
Set wks = Me.Worksheets("Sheet1") 'change this if required
Set rng = wks.Range("A1")
With rng
If IsDate(.Value) And IsDate(.Offset(0, 1).Value) Then
If .Offset(0, 1).Value - .Value 30 Then
MsgBox "Saving not possible - old workbook"
Cancel = True
End If
End If
End With
End Sub


Private Sub Workbook_Open()
Dim wks As Worksheet
Dim rng As Range
Set wks = Me.Worksheets("Sheet1") 'change this if required
Set rng = wks.Range("A1")
With rng
If rng.Value = "" Then
.Value = Date
.NumberFormat = "MM-DD-YYYY"
ElseIf IsDate(.Offset(0, 1).Value) Then
If .Offset(0, 1).Value - .Value 30 Then
MsgBox "Old Workbook - Don't enter anything"
End If
End If
End With
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

"CLR" schrieb im Newsbeitrag
...
Hi All...........

I have a workbook with Sheet1 protected on our network that is a
WorkOrder
Form. It is revised regularly. Users go to the network and download
this
file to their computer or laptop and fill it out and save it to
another
directory under another name. I want to be sure they always use the
latest
version of the file. So, to that end, I believe I would like to do
the
folllowing, unless someone knows a better way.

1-I want a macro to automatically insert the =now() function in A1,
AND
convert it over to a frozen value for that point in time.......this
will
tell me exactly when they downloaded the file, especially for the
notebook
guys. This would be a one-time function and work only when they
first
download the filel.

2-Cell A2 normally gets a date inserted by the user. When they do
this, I
would like to measure the difference between this date and the frozen
date
in A1 and if it exceeds 30 days, to have a pop-up message requesting
them to
"Please use the more current version of the form". No further data
entry
could then be made to that file.

Is this approach feasible?...........or might something else fulfill
my
needs better?

Any help would be appreciated,

Vaya con Dios,
Chuck, CABGx3

Got it working today Frank, many many thanks...........could not have gotten
there without your kind help...........

Vaya con Dios,
Chuck, CABGx3


"Frank Kabel" wrote in message
...
Hi
try the following code. Put this in the workbook module of your file
(not in a standard module):

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim wks As Worksheet
Dim rng As Range
Set wks = Me.Worksheets("Sheet1") 'change this if required
Set rng = wks.Range("A1")
With rng
If IsDate(.Value) And IsDate(.Offset(0, 1).Value) Then
If .Offset(0, 1).Value - .Value 30 Then
MsgBox "Saving not possible - old workbook"
Cancel = True
End If
End If
End With
End Sub


Private Sub Workbook_Open()
Dim wks As Worksheet
Dim rng As Range
Set wks = Me.Worksheets("Sheet1") 'change this if required
Set rng = wks.Range("A1")
With rng
If rng.Value = "" Then
.Value = Date
.NumberFormat = "MM-DD-YYYY"
ElseIf IsDate(.Offset(0, 1).Value) Then
If .Offset(0, 1).Value - .Value 30 Then
MsgBox "Old Workbook - Don't enter anything"
End If
End If
End With
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

"CLR" schrieb im Newsbeitrag
...
Hi All...........

I have a workbook with Sheet1 protected on our network that is a
WorkOrder
Form. It is revised regularly. Users go to the network and download
this
file to their computer or laptop and fill it out and save it to
another
directory under another name. I want to be sure they always use the
latest
version of the file. So, to that end, I believe I would like to do
the
folllowing, unless someone knows a better way.

1-I want a macro to automatically insert the =now() function in A1,
AND
convert it over to a frozen value for that point in time.......this
will
tell me exactly when they downloaded the file, especially for the
notebook
guys. This would be a one-time function and work only when they
first
download the filel.

2-Cell A2 normally gets a date inserted by the user. When they do
this, I
would like to measure the difference between this date and the frozen
date
in A1 and if it exceeds 30 days, to have a pop-up message requesting
them to
"Please use the more current version of the form". No further data
entry
could then be made to that file.

Is this approach feasible?...........or might something else fulfill
my
needs better?

Any help would be appreciated,

Vaya con Dios,
Chuck, CABGx3

Hi
thanks for your feedback :-)

--
Regards
Frank Kabel
Frankfurt, Germany


CLR wrote:
Got it working today Frank, many many thanks...........could not have
gotten there without your kind help...........

Vaya con Dios,
Chuck, CABGx3