Please use the latest Version

C

CLR

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
 
F

Frank Kabel

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
 
C

CLR

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
 
C

CLR

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

Vaya con Dios,
Chuck, CABGx3
 
Top