PDA

View Full Version : Customising Comments



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

Bob Phillips
02-25-2009, 06:23 AM
That means that you have two or more SelectionChange procedures in your sheet. You can only have one, you need to merge the code in them to a single procedure.

dgt
02-25-2009, 07:58 AM
Hi xld

Thanks for the quick reply. I do have another PrivateSub Worksheet_SelectionChange(ByVal Target As Excel.Range) in that worksheet.

Managed to play around with the code and ended up with the following combined code, which appears to be working!



'Code to be placed in the worksheet(s) you want to use this in
Option Explicit
Public oldRange As Range

Private Sub 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
If Not .Comment Is Nothing Then
If .Comment.Visible = False Then
.Comment.Visible = True
Else
.Comment.Visible = False
End If
End If
End With

Set oldRange = Target(1, 1)
'Code for sequencing entries in Column A
Dim Col As Long
Dim i As Integer, x As Integer
Dim LastRow As Long
Dim StartCell As String
Dim StartRow As Long

StartCell = "I5"

With Range(StartCell)
Col = .Column
StartRow = .Row
LastRow = Cells(Rows.Count, Col).End(xlUp).Row
End With

For i = StartRow To LastRow
If Cells(i, 2).Value <> "" Then
x = x + 1
Cells(i, 1).Value = "S" & x
End If
Next i
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
On Error Resume Next
Dim cmtText As String
Dim inputText As String

If Target.Comment Is Nothing Then
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")
End If
Target.ClearComments
Target.AddComment Text:=cmtText
Target.Comment.Visible = True
Target.Comment.Shape.TextFrame.AutoSize = True 'Remove if you want to size it yourself
End If

Cancel = True 'Remove this if you want to enter text in the cell after you add the comment
End Sub


However, the combined code seems to be creating a jerky screen movement at times but cannot give you a better explanation for this.

This line appears to make no difference whether it is left in or removed. Text can still be added to the cell either way.

Cancel = True 'Remove this if you want to enter text in the cell after you add the comment

The AutoSize for the comment window works on new or edited old comments but does not resize existing unedited comments but that is not a problem.

Double clicking the comment window allows it to be edited and exited from within the window; however additional text entered via the InputBox causes the comment to lose font properties such as Bold etc.

I would still like to make the comments only visible when the cell is either single clicked or double clicked but have no idea of how to do this. I have tried changing the Target.Comment.Visible to False but again that seems to make no difference.

Any suggestions …dgt

dgt
03-01-2009, 03:17 PM
<<< bump >>>