user add cell comments in protected sheets

[mweaver]

Greetings from Salina, Ks,

I have a worksheet for other users in our office that is worksheet protected for obvious reasons. Complete access to the worksheet has proved fatal. We keep the worksheet protected to maintain the formulas & formatting.

This protection disables the users ability to add cell comments which would be very helpful in this worksheet.

Is there simple code to create a macro that would :

1. password unprotect worksheet

2. Prompt user for cell comment

3. Password protect sheet upon completion of user entering cell comment?



Any help would be appreciated.

mweaver

 

[PaddyD]

Shouldn't be too traumatic, but a couple of questions:

- I guess you imagine something like users select the fell they want to comment, then click a button or something to get a box where they enter text that becomes the comment?

- can users comment a cell twice?
- if so, what happens to the previous comment
- if so, do you want to record the name / id etc of the user adding the comment
- can users delete comments?

Paddy

 

[mweaver]

PaddyD;

Thanks for the response!

Multiple cell comments and user deletion would be OK for this worksheet. Recording Id would be nice but not really necessary.

This worksheet is not really shared. Each user has individual copies but cannot seem to be trusted to leave formatting and formulas alone. The comments would be personal notes regarding cell entries to allow the user notation for why he did-- what he did 20+ days later.


any help would be appreciated.


Thanks PaddyD

mweaver

 

[Derek]

Hi there
If your sheet doesn't contain objects, try protecting without checking the objects box.
regards
Derek

 

[PaddyD]

On 2002-09-03 19:54, Derek wrote:
Hi there
If your sheet doesn't contain objects, try protecting without checking the objects box.
regards
Derek

Well spotted Derek - don't I feel silly now!

 

[Derek]

Don't worry Paddy, I have been filching plenty of good ideas from all your posts.
regards
Derek

 

[mweaver]

Thanks Derek!

That is Wonderful... but .... This worksheet contains objects which must be protected!

mweaver

 

[PaddyD]

Bugger, another simple, elegant solution shot down by the messiness of the real world!


Anyway, how's your coding?

see here:

http://mrexcel.com/board/viewtopic.php?topic=19869&forum=2

which will need a textbox to take the comment string instead of picking it from a cell. All of which can be inserted into something like:<pre>
Sub Comments()

Dim WS As Worksheet

WS.Unprotect "password"

'comment code here

WS.Protect "password"

End Sub</pre>

paddy
This message was edited by PaddyD on 2002-09-03 20:36