I want to remind users of a workbook that they need to fill in a last updated
cell if they have made any amendments to the sheet.

Is there a way I can get a message to pop up when they go to save the
document so they have to input the 'Last Updated' date in the relevant column
before they save and exit. And if I'm feeling really evil, can I get it to
not allow them to exit unless they've done it?

ps I'm a total VBA novice!

You need to go into the vba editor alt F11

goto the thisworkbook object in the project explorer, then choos
Workbook and the BeforeClose event

then you want the msgbox function or you could even just update th
cell automatically.

Hope this points you in the right direction.

Cheer

--
tony
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=54469

You could as well automatically poputalete thet 'Last Updateted' cell
whenever you save the workbook.

That would be a lot easier!

Cheers,
--
AP

"EJ" a écrit dans le message de news:
...
I want to remind users of a workbook that they need to fill in a last
updated
cell if they have made any amendments to the sheet.

Is there a way I can get a message to pop up when they go to save the
document so they have to input the 'Last Updated' date in the relevant
column
before they save and exit. And if I'm feeling really evil, can I get it
to
not allow them to exit unless they've done it?

ps I'm a total VBA novice!

see Chip Pearson's introduction to events:
http://www.cpearson.com/excel/events.htm

the event you would use would be the beforeclose and/or the beforesave
events for the workbook.

--
Regards,
Tom Ogilvy


"EJ" wrote:

I want to remind users of a workbook that they need to fill in a last updated
cell if they have made any amendments to the sheet.

Is there a way I can get a message to pop up when they go to save the
document so they have to input the 'Last Updated' date in the relevant column
before they save and exit. And if I'm feeling really evil, can I get it to
not allow them to exit unless they've done it?

ps I'm a total VBA novice!

Thanks all.

I used the Before Save option in the end and wrote a suitably snotty
message. ha ha!

To automatically update the cells with the date would be great, but as
theres a 'Last Updated' cell on each line of a 500 line document and not
every line gets updated this wouldn't be applicable. A great idea for another
document though!

Well lots of ways to do that but an easy one to conceptualise ( and
fairly fool proof) with a simple spreadsheet.

On opening the workbook make a copy of the sheet and hide it. On
closing the workbook run down each line comparing the current value
with the corresponding value on the saved sheet, update the last
changed date as appropriate.

Gets sort of complicated if you change rows around and that sort of
thing then you need to use the cell change event. But these are usually
problematic for a beginner to debug.


Just to get you thinking.


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=544696