Microsoft Office Forums Automatically enter date into a column and make that column read only

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-17-2012, 10:40 PM
Mr Davo Mr Davo is offline Automatically enter date into a column and make that column read only Windows 7 64bit Automatically enter date into a column and make that column read only Office 2010 32bit
Novice
Automatically enter date into a column and make that column read only
 
Join Date: Jun 2011
Posts: 20
Mr Davo is on a distinguished road
Default Automatically enter date into a column and make that column read only

Hi Everyone,



I have a date column in my spreadsheet and there are several actions that I would like to apply to it -

1). Auto populate the date column with the current date only when the column to the left has data entered into it (please see below)



2). Make the date column read only so that it cannot be modified by other parties.

Any help will be greatly appreciated.

Kind Regards,

Davo
Reply With Quote
  #2  
Old 10-29-2012, 01:07 AM
macropod's Avatar
macropod macropod is offline Automatically enter date into a column and make that column read only Windows 7 64bit Automatically enter date into a column and make that column read only Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,478
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

You can do this with a 'Worksheet_Change' macro in the relevant worksheet module.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, ActiveSheet.Range("B:B")) Is Nothing Then Exit Sub
ActiveSheet.Unprotect
If IsDate(Target.Value) Then Target.Locked = True
ActiveSheet.Protect
End Sub
To use the macro, first format all cells as neither locked nor hidden. Then apply worksheet protection. The macro above assumes no protection password.

To install the macro, press Alt-F11 to open the vb editor, then select and open the relevant worksheet module from the panel on the left-hand side of the screen. Copy & paste the code to that module, then press Alt-F11 again to return to the workbook. Note that dates only be protected - other content will not be protected.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Long, 3 Column Table - Can I make Fit Into Page Columns? Rigwald Word Tables 9 08-07-2012 08:14 PM
Is there a way to automatically highlight the column and the row that of the current Jamal NUMAN Excel 8 02-14-2012 02:58 PM
Make row in one sheet equal column of another hypertyper Excel 6 01-31-2012 03:48 PM
Automatically enter date into a column and make that column read only Can I change the horizontal scrollbar to scroll smoothly rather than column by column carpat Excel 0 01-10-2012 09:34 AM
Automatically enter date into a column and make that column read only Need to search a column for a macth and return a result from a third column pdfaust Excel 2 02-03-2011 03:02 PM


All times are GMT -7. The time now is 04:08 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Search Engine Optimisation provided by DragonByte SEO v2.0.37 (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft