Solved: Protection issue with comments

[creativef]

Hi,

This is my first post - so please treat me gently!

I found the following code somewhere else on this site (thanks malik641) for creating a input box to enter in comments. It also allows comments to be added to - brilliant.

Option Explicit
Public oldRange As Range

PrivateSub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next
Dim rng As Range
Set rng = Target(1, 1)

oldRange.Comment.Visible = False

With rng
IfNot .Comment IsNothingThen
If .Comment.Visible = FalseThen
.Comment.Visible = True
Else
.Comment.Visible = False
EndIf
EndIf
End With

Set oldRange = Target(1, 1)
End Sub

PrivateSub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel AsBoolean)
On Error Resume Next
Dim cmtText AsString
Dim inputText AsString
ActiveSheet.Unprotect "password"
If Target.Comment IsNothingThen
cmtText = InputBox("Enter info:", "Comment Info")
If cmtText = "" Then Exit Sub
Target.AddComment Text:=cmtText
Target.Comment.Visible = True
Target.Comment.Shape.TextFrame.AutoSize = True'Remove if you want to size it yourself
Else
If Target.Comment.Text <> "" Then
inputText = InputBox("Enter info:", "Comment Info")
If inputText = "" Then Exit Sub
cmtText = Target.Comment.Text & Chr(10) & inputText
Else
cmtText = InputBox("Enter info:", "Comment Info")
EndIf
Target.ClearComments
Target.AddComment Text:=cmtText
Target.Comment.Visible = True
Target.Comment.Shape.TextFrame.AutoSize = True'Remove if you want to size it yourself
EndIf

However, I have an issue when I use it on a protected sheet. I can unprotect the sheet (see the code in red) but my attempts to re-protect it don't work, including a major problem if someone hits the 'Cancel' button without entering a comment the whole sheet remains unprotected.

I'm sure this is easy to solve, but it is beyond my ability/understanding of the original code. Can anyone help?

Simon

[shamsam1]

try this

Option Explicit
Public oldRange As Range

PrivateSub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next
Dim rng As Range
Set rng = Target(1, 1)

oldRange.Comment.Visible = False

With rng
IfNot .Comment IsNothingThen
If .Comment.Visible = FalseThen
.Comment.Visible = True
Else
.Comment.Visible = False
EndIf
EndIf
End With

Set oldRange = Target(1, 1)
End Sub

PrivateSub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel AsBoolean)
On Error Resume Next
Dim cmtText AsString
Dim inputText AsString
ActiveSheet.Unprotect "password"
If Target.Comment IsNothingThen
cmtText = InputBox("Enter info:", "Comment Info")
If cmtText = "" Then Exit Sub
Target.AddComment Text:=cmtText
Target.Comment.Visible = True
Target.Comment.Shape.TextFrame.AutoSize = True'Remove if you want to size it yourself
Else
If Target.Comment.Text <> "" Then
inputText = InputBox("Enter info:", "Comment Info")
If inputText = "" Then Exit Sub
cmtText = Target.Comment.Text & Chr(10) & inputText
Else
cmtText = InputBox("Enter info:", "Comment Info")
EndIf
Target.ClearComments
Target.AddComment Text:=cmtText
Target.Comment.Visible = True
Target.Comment.Shape.TextFrame.AutoSize = True'Remove if you want to size it yourself

ActiveSheet.protect Password:="MyPassword"

EndIf

[creativef]

Thanks shamsam for this. It protects my sheet correctly if the user enters the comment as expected and clicks on the 'OK' button on the input box but if the user clicks 'Cancel' it leaves the sheet unprotected on the current cell, which I don't want in case someone accidently wipes over the data. Any way to stop this from happening?

[shamsam1]

just add this code in cancel even

just double click cancel button then add

ActiveSheet.protect Password:="MyPassword"




else send me ur excel ..i will do the needful
regards
sam

[creativef]

Sorry Sam, I don't understand how I can alter the code for the cancel button - and as a newbie I can't e-mails, Sorry.

[creativef]

Is there anyone who can help with this problem?

[shamsam1]

in aAdditional Options there is attachment..u can attach ur work book ,,just scroll down u will get it...



got it...

Miscellaneous Options Automatically parse links in text
Disable smilies in text

Attach Files Valid file extensions: accdb bmp doc docm docx gif jpeg jpg pdf png xls xlsm xlsx zip


Manage Attachments

Thread Subscription Notification Type:
Do not subscribe No email notification Instant email notification Daily email notification Weekly email notification Rate Thread If you like, you can add a score for this thread.
Choose a rating 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

[creativef]

Here is a basic version of file. When you double click anywhere on the screen the comment box comes up. However, click the cancel button and it unprotects the sheet and I need the sheet to remain protected.

I also have the key combination of Ctrl and / to remove the current cell comment. Is there anyway to edit the comments using vba - i.e. once they have been written, rather than deleting the whole thing?

[malik641]

The problem you get when you press cancel is at the line:
[vba]If cmtText = "" Then Exit Sub[/vba]
Since you are unprotecting the sheet before the Input box and you are re-protecting it at the end of the code, if the user presses cancel then it leaves the sub BEFORE it is re-protected. Try changing this:
[vba]If cmtText = "" Then Exit Sub[/vba]
To this:
[vba]If cmtText = "" Then
ActiveWorksheet.Protect Password:="MyPassword"
Exit Sub
End If[/vba]

That should help. Let us know

[creativef]

This is brilliant, thanks Malik - it works a treat.
Just as an aside - is it possible to add code so the user can edit an existing comment that has been entered already, rather than just keep adding more all the time?

Simon

[malik641]

No problem.

About your other request, I think that's a little difficult if you are using protected worksheets. I can probably make it so when the user double-clicks, it will ask if they want to edit. If yes, then I think I can have the whole text selected and they can edit, but to do that I have to unprotect the worksheet. The problem with that is, to re-protect the worksheet you have to KNOW exactly when the user is done editing the comment. And how would you determine that? I don't know if there are event handlers to help us with that task. If there are, it might be a hefty task to implement.

The reason it's so nice to use the method you are using now is that you know exactly when you need to unprotect and re-protect the worksheet, because you are the one controlling when the info is entered. Letting the user edit the text is much less controlled. Letting the user have control of the comment while the worksheet is unprotected worries me about security issues.

...on the other hand, if you protect the worksheet and set "Edit Objects" to true, then they can edit the comment without unprotecting the sheet and without much additional code.

You know what? Try that. When you protect the worksheet, set "Edit Objects" to True. Like this:

[vba]ActiveSheet.Protect Password:="MyPassword", DrawingObjects:=False[/vba]

The thing you may not like with this is that users can also move the comment and resize, etc.