The short answer is to save the information in the workbook - but you seem
to indicate that the users commonly open the workbook and close it without
saving and your criteria is to archive it after 5 of these if they have
elapsed. If so, putting information on the number of openings since the
last save would be fruitless as it would never get saved - and the next
opening would always appear as the first opening since the last save. The
solution then it to write to the registry or write to a text file (commonly
called a log file when used for this purpose). This can be done in the
workbook_open event.

The registry is a poor choice if this is on a network drive and will be
opened by different people or any other situation in which multiple people
will open it with different login ids.

So you could write information to a separate file using low level file io:

http://msdn.microsoft.com/library/de...ce10032002.asp
Working with Files, Folders and Drives: More VBA Tips and Tricks by David
Shank

http://www.applecore99.com/gen/gen029.asp

--
Regards,
Tom Ogilvy

"CLR" <CLR@discussions.microsoft.com> wrote in message
news:018F47A6-5D2D-4675-BF03-8165172B3058@microsoft.com...
> Thank you Sir........your code worked perfectly for what I
asked.......only
> thing is, I asked a little bit wrong<g>........."what the Lieutenant
REALLY
> meant to say", was, when the book opens I want the message box to ask the
> question,(just like it does), and if the user says no, they are permitted
to
> go ahead and use the file at will, but on the fifth open from the most
recent
> save, (or maybe 5 days chronologically, if thats easier) the book must be
> saved before it can be used again.
>
>
> And, thanks for the tip about the SendKeys.....It was for the messagebox
> that stopped the code before, (done several years ago).......I replaced it
> with your suggestion and it works super...
>
> Thanks again,
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
>
> "Tom Ogilvy" wrote:
>
> > If it must be archived and you already know what the name should be, why
> > bother the user. Just archive it.
> >
> > In any event, you can use workbook_Open event and loop until the user
says
> > yes or keep track in the loop and archive it after 5 knows.
> >
> > Private Sub Workbook_Open()
> > for i = 1 to 5
> > ans = msgbox( "Must be archived, do it now?",vbYesNo)
> > if ans = vbYes then exit for
> > Next
> > ' save the workbook
> > End Sub
> >
> > Why are you using sendkeys. If it is because you want to overwrite a
file,
> >
> > Application.DisplayAlerts = False
> > ActiveWorkbook.SaveAs FName
> > Application.DisplayAlerts = True
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "CLR" <CLR@discussions.microsoft.com> wrote in message
> > news:4EBD6796-7844-46F3-BBEF-888BB832BD70@microsoft.com...
> > > Hi All....
> > >
> > > I have a nifty piece of code (aquired from you fine folks) that does
an
> > > excellent job of archiving a critical File that I made. The only
problem
> > is,
> > > that the user never presses the button and cause it to run. My
question
> > is,
> > > is it possible to modify this code so that it will pop-up a message
asking
> > > the user if he wishes to Archive, each time the WorkBook is opened,
and if
> > he
> > > keeps saying NO, then to inform him that if he wishes to proceed
(after
> > say 5
> > > NO's) that the WorkBook MUST be Archived.......or something to that
> > effect.
> > >
> > > Here's the working code.....
> > >
> > > Sub SaveArchive()
> > > ' Saves the workbook to a predetermined Archive Directory and appends
date
> > > and time to filename,
> > > ' then re-configures file so it will naturally be saved to the
directory
> > > from whence it came.
> > >
> > > CurrentPath = CurDir
> > > ArchivePath = "T:\#tools\_ToolRoomArchive\"
> > > WorkBookName = ActiveWorkbook.Name
> > > FName = ArchivePath
> > > FName = FName + Worksheets("All WO's").Range("AH36").Value 'Used
to
> > name
> > > archived file to cell value
> > > FName = FName + Str(Hour(Time)) + "_" + Str(Minute(Time)) + "_" +
> > > Str(Second(Time))
> > > FName = FName + "_" + Str(Month(Date)) + "_" + Str(Day(Date)) +
"_" +
> > > Str(Year(Date))
> > > ActiveWorkbook.SaveAs FName
> > > FName = CurrentPath + "\" + WorkBookName
> > > SendKeys "Y"
> > > ActiveWorkbook.SaveAs FName
> > > End Sub
> > >
> > >
> > > Any help would be much appreciated....
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> >
> >
> >