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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
sijpieCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.