Add a comment on cell content change with VBA

[Les]

Hello all, i have a spreadsheet that is protected and shared. When the
user logs in it "unlocks" a certain range of cells for him to update.
I would like to insert a comment when he/she updates the cell, but the
worksheet "Change" function does not work if it is shared & protected.
Is there a way around this ??

 

[Dave Peterson]

The worksheet_change event should still fire.

I bet you're trying to unprotect the worksheet, add the comment (a real comment
or just text in a different cell????), then reprotect the worksheet.

If that's correct, then you've seen that you can't change the worksheet
protection of a shared workbook.

 

[Les]

Hi Dave, yes that is what i am trying to do.

I am trying to put in the real comment as to who did the change.

Is there a way to do this ??

 

[Dave Peterson]

You can protect the worksheet, but allow the user to edit objects (including
Comments and other objects). If you have pictures/buttons/comboboxes..., then
these will be unprotected, too.

In xl2003 menus:
Unshare the workbook
Unprotect the worksheet
reprotect the worksheet: Tools|Protection|protect sheet, but check the "edit
objects" under the Allow all users of this worksheet to" box.
Share the workbook

and test it out.

 

[Les]

Thanks Dave, much appreciated.

You can protect the worksheet, but allow the user to edit objects (including
Comments and other objects).  If you have pictures/buttons/comboboxes..., then
these will be unprotected, too.

In xl2003 menus:
Unshare the workbook
Unprotect the worksheet
reprotect the worksheet:  Tools|Protection|protect sheet, but check the "edit
objects" under the Allow all users of this worksheet to" box.
Share the workbook

and test it out.









--

Dave Peterson- Hide quoted text -

- Show quoted text -