Customising Comments

[dgt]

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]

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]

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]

<<< bump >>>