Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Excel - Lock date in multiple cells

I have an Excel Template .xlt that is distributed and saved by multiple
users as an Excel spreadsheet. I want to have an audit trail visible to
show when entries were made in particular cells. Cells L6, D8, L8 & D10
are to be filled, possibly on different dates, with the relevant users
name. As, and only when, a name is entered into each of those cells I
would like a date (in dd.mm.yyyy format, the format isn't a problem) to
appear in cells P6, H8, P8 & H10 respectively. I want that date to
remain constant even when the .xls is saved, closed and reopened on a
different date.

To explain a little better maybe an example will help:

Name entered in L6 on 29/08/2006 should result in 29.08.2006 appearing
in P6.
Name entered in D8 on 01/09/2006 should result in 01.09.2006 appearing
in H8.
Name entered in L8 on 02/09/2006 should result in 02.09.2006 appearing
in P8.
Name entered in D10 on 03/09/2006 should result in 03/09/2006 appearing
in H10.
If the .xls is opened again on 04/09/2006 the desired result is:

L6 Joe Bloggs P6 29.08.2006
D8 Billy Bloggs H8 01.09.2006
L8 Barry Bloggs P8 02.09.2006
D10 Jack Bloggs H10 03.09.2006

I would appreciate your assistance greatly,
Lindsay

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 510
Default Excel - Lock date in multiple cells

Hi Lindsay,

With an event macro stored into the sheet module :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$8" Or Target.Address = "$D$10" _
Or Target.Address = "$L$6" Or Target.Address = "$L$8" Then
Target.Offset(0, 4) = Now
Else
Exit Sub
End If
End Sub


HTH
Cheers
Carim

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Excel - Lock date in multiple cells

Dear Carim,
That worked really well, thankyou. I had to adjust the offset a little
to cope with some merged cells but after that it worked well.
Thanks for your help.
Lindsay
Carim wrote:
Hi Lindsay,

With an event macro stored into the sheet module :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$8" Or Target.Address = "$D$10" _
Or Target.Address = "$L$6" Or Target.Address = "$L$8" Then
Target.Offset(0, 4) = Now
Else
Exit Sub
End If
End Sub


HTH
Cheers
Carim


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 510
Default Excel - Lock date in multiple cells


Hi Lindsay,

Glad I could help ...
Cheers
Carim

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
How do I insert a symbol AT THE END of text for multiple cells in Excel? [email protected] Excel Worksheet Functions 3 June 19th 06 04:47 PM
Can I program Excel cells to change colour at a set date? sunflowermurray Excel Discussion (Misc queries) 1 September 8th 05 12:22 PM
EXCEL, VSTO: Fastest way to access multiple cells Alan Excel Discussion (Misc queries) 1 June 1st 05 02:45 PM
How do I center data across multiple cells in excel without mergi. lmark Excel Discussion (Misc queries) 6 January 27th 05 06:55 PM


All times are GMT +1. The time now is 07:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"