Possibility to quick modify protected cells in shared documents

[AA Arens]

I am sharing a workbook with my staff. I am looking for a possibility
to change a cell content from specific cell while the other users
cannot. (I know you can lock the cells and un-protect, but that's only
possible if I disable sharing)

The best would be if a password request pop-up appears after I focus
on the cell.
What is the VBcode if I need it?

Bart
2003

 

[Dave Peterson]

Your code won't be able to unprotect a worksheet either--the same rules for
sharing applies to it.

But you can use:
Tools|Protection|Allow users to edit ranges
and give that range a password that only you know.

You'll have to do this with the worksheet unprotected. That means that you'll
have to unshare the workbook for a little time, too.

 

[AA Arens]

Your code won't be able to unprotect a worksheet either--the same rules for
sharing applies to it.

But you can use:
Tools|Protection|Allow users to edit ranges
and give that range a password that only you know.

You'll have to do this with the worksheet unprotected.  That means thatyou'll
have to unshare the workbook for a little time, too.

Hi Dave,

It seems not to help, the cells are either editable (I set to
unlocked) or not editable (I set to unlocked) after I protect the
file. There is not password request at all.

I have set a range of cells in the dialog box of the function you
mentioned, set the password, but not add users, so only I am able to
edit the cell.

Do I have to set the cells in the range to lock or unlocked?

Bart

 

[Dave Peterson]

The range should be locked.

If this doesn't help, share the exact steps you tried.

 

[AA Arens]

Hi Dave,

It seems not to help, the cells are either editable (I set to
unlocked) or not editable (I set to unlocked) after I protect the
file. There is not password request at all.

I have set a range of cells in the dialog box of the function you
mentioned, set the password, but not add users, so only I am able to
edit the cell.

Do I have to set the cells in the range to lock or unlocked?

Bart

Ok, I managed to have it working, only to discover that the user is
able to select locked cells. Wish I could have set the "Allow user to
edit range" for unlocked cells while the other cells remains locked
and the sheet protection does not allow users to select locked cells.

 

[AA Arens]

The range should be locked.

If this doesn't help, share the exact steps you tried.

It even only works when the cells are locked while the sheet
protection should be set to "allow select locked cells. But dubious.

Bart

 

[Dave Peterson]

If the cell is locked and you choose to allow the user not to select locked
cells, then how would you get to the cell to even get asked for a password.

 

[AA Arens]

If the cell is locked and you choose to allow the user not to select locked
cells, then how would you get to the cell to even get asked for a password.

I thought the program is verifying the existence of the "Allow user
to
edit range" at a locking cell before either, if yes, ask for password
or, if not, just leave it blocked.

Thanks indeed.

 

[Dave Peterson]

But the only way that excel knows what cell of the 65536*256 (16,777,216!) cells
you want to edit is by you selecting it.

If you don't allow the user to select the cell, then excel won't know that you
really want someone to be able to change it.

Another alternative would be to hide the worksheet and build yourself a
userform. You'd have complete control over what could be changed and by whom.