dgt
02-25-2009, 05:59 AM
Hi all
I’m hoping that someone will be able to resolve this problem for me in that way that I want cell comments to be handled on a particular worksheet.
Firstly, I need to stop the contents of the comment being displayed when you roll the mouse over the cell but still leave the red triangle visible in the corner.
Second, I would like to only display the contents of the comment when the cell is single clicked and to be able to add or edit the comment by double clicking the cell.
It would help if the comments could be auto-sized to fit the contents of the comment not the cell. Cells may also have their own content and may be subject to conditional formatting.
My knowledge of VBA is virtually nil but I will try to have a go at changing a piece of code! I discovered this piece of code on this forum but could not get it to work at all.
When run, it comes up with a compile error:–
Ambiguous Name Detected - Worksheet_SelectionChange
and also corrupts a different column which uses a VBA Custom Function contained in a module.
'Code to be placed in the worksheet(s) you want to use this in
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
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
Cancel = True'Remove this if you want to enter text in the cell after you add the comment
End Sub
I am using Excel 2003 and all the comments are stored in column N of a particular worksheet.
TIA …dgt
I’m hoping that someone will be able to resolve this problem for me in that way that I want cell comments to be handled on a particular worksheet.
Firstly, I need to stop the contents of the comment being displayed when you roll the mouse over the cell but still leave the red triangle visible in the corner.
Second, I would like to only display the contents of the comment when the cell is single clicked and to be able to add or edit the comment by double clicking the cell.
It would help if the comments could be auto-sized to fit the contents of the comment not the cell. Cells may also have their own content and may be subject to conditional formatting.
My knowledge of VBA is virtually nil but I will try to have a go at changing a piece of code! I discovered this piece of code on this forum but could not get it to work at all.
When run, it comes up with a compile error:–
Ambiguous Name Detected - Worksheet_SelectionChange
and also corrupts a different column which uses a VBA Custom Function contained in a module.
'Code to be placed in the worksheet(s) you want to use this in
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
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
Cancel = True'Remove this if you want to enter text in the cell after you add the comment
End Sub
I am using Excel 2003 and all the comments are stored in column N of a particular worksheet.
TIA …dgt