creativef
09-04-2008, 09:01 AM
:banghead: 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
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