How do I protect cells in Excel 2003 after data is entered but allow controlled access to one particular column?

Hi, I have a spreadsheet which must not be changed once data is entered on a specific row, apart from one column (T) which may be amended at a later stage but should be password protected to prevent accidental changes. Users must not be able to unprotect the rest of the sheet or know the password(s). I have tried a locking cell on changes and this works relatively OK but it locks the cell and the user can't click on the cell in column T if they want to change that particular cell details. I thought running a macro might be the answer but I can't get it to lock the spreadsheet afterwards.
james252Asked:
Who is Participating?
 
sijpieConnect With a Mentor Commented:
Sorry, forgot yto put the code in the correct format
Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Unprotect Password:=""
    If Intersect(Target, Range("T:T")) Is Nothing Then
        Target.Locked = True
    end if
    ActiveSheet.Protect Password:="", DrawingObjects:=True, _
        contents:=True, Scenarios:=True, _
        userinterfaceonly:=True
End Sub

Open in new window

0
 
CORBETTMJCommented:
James

Not sure if this is what you mean but if you select column T then right click,  select format cells, click on the Protection Tab, then uncheck Locked cells. This will allow the user to alter these cells once the rest of the sheet is protected

Alternatively you you can add activesheet.protect("Password") at the end of your macro

Hope this helps

0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello James,

I'm not quite sure I understand the requirements completely, but if you select column T, then open the format dialog and on the Protection tab unselect "locked", then protect the sheet, users should be able to change the values in column T only.

All other cells in the sheet should remain at "locked"

cheers, teylyn
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ydsonlineCommented:
I'd probably suggest you use "allow user to edit ranges" feature in excel which gives you the ability to specify which locked cell/s you are allowed to edit by means of a password. All other locked cells will be locked as normal. Off course using this feature still requires you to lock the worksheet with a password. The password you specify under "allow user to edit ranges" does not need to be the same as the password protecting the sheet.
To get to this feature:
  • Excel 2003: Tools > Protection > "Allow Users to Edit Ranges..."
  • Excel 2007: Review Ribbon Tab > "Allow Users to Edit Ranges" under Changes section.

 
0
 
ydsonlineCommented:
Just to clarify as well, the cells that you want to allow the user to edit without a password needs to be unlocked by selecting those cells and right click > "format cells" > "Protection Tab" > Untick "Locked" which I think is similar to what Teylyn suggested but needs to be used in conjuction with the "allow users to edit ranges" feature if you want it to work properly.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
ydsonline, I always manage to forget this feature, but it's really handy if you want separate user groups to be able to edit different parts of a sheet while protecting the rest of the sheet.

So, James, if simply unlocking column T and then protecting the sheet is not sufficient, ydsonline has a great alternative worth exploring!

cheers, teylyn
0
 
james252Author Commented:
maybe I wan't very clear in my initial description, I'll try again. A user must be able to enter data across all columns a row at a time but as the cell is changed it must then become locked. This is also to apply to the column that may need changing but this column should be "unlockable" to allow updates but must then return to locked state (upto the next clear row, lets say where column C is empty as A and B have pretyped data in them and column T doesn't have to have anything at all)  once the change has been made.
0
 
sijpieCommented:
If I understand you correctly you want to have all clear cells unlocked and as soon as data is filled out to have the cell locked. You can write a macro in the sheet module that will set the cell property locked on change.
Copy the code below to the module of the sheet in question (right click on the sheet tab and select 'view Code')
Then set all the blank cells to format/cell/protection and untick the locked box.
Then protect your sheet (tools/protection/protect sheet)
Now as soon as yu have entered value in a cell the cell is locked.

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Unprotect Password:=""
    Target.Locked = True
    ActiveSheet.Protect Password:="", DrawingObjects:=True, _
        contents:=True, Scenarios:=True, _
        userinterfaceonly:=True
End Sub

Open in new window

0
 
james252Author Commented:
Sorry, been off for a while. The solution suggested by sijpie is halfway there, and is what I have at the moment, but the users need to make changes to column T and this gets locked when changed, as with all cells on the sheet. Is there a way of leaving this particular column unlocked when changed and still lock all other columns when data is entered?
thanks
James
0
 
sijpieCommented:
Now I understand. Column T needs to be available for change, only the other cells need locking once changed.

[code]
 Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Unprotect Password:=""
    If Intersect(Target, Range("T:T")) Is Nothing Then
        Target.Locked = True
    end if
    ActiveSheet.Protect Password:="", DrawingObjects:=True, _
        contents:=True, Scenarios:=True, _
        userinterfaceonly:=True
End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.