VBA to close comment box

[Shadkng]

Hi, I got the below code from the internet which I then modified a bit, which opens a comment box. Is it possible to have the box close when clicking on another cell? Thanks

Sub addNewComment()
    Selection.Interior.ColorIndex = 44  'interior color of selected cell
    ActiveCell.AddComment _
   
                With ActiveCell.Comment
            
                With .Shape
                    .AutoShapeType = msoShapeFoldedCorner
                    .Fill.ForeColor.RGB = RGB(215, 224, 239)
                    .Width = 150
                    .Height = 75
                    
                    With .TextFrame
                        .Characters.Font.Size = 11
                        .Characters.Font.Name = "Calibri"
                    End With
                End With
                .Visible = True
                .Shape.Select True
            End With
            End Sub

 

[Michael M]

Woudn't it be easier to simply only have the comment visible when the cell is selected??
Remove these 2 lines from your code

                '.Visible = True
                '.Shape.Select True

 

[Shadkng]

Woudn't it be easier to simply only have the comment visible when the cell is selected??
Remove these 2 lines from your code

                '.Visible = True
                '.Shape.Select True

If I remove those then the box doesn't open.

 

[Michael M]

It will open when you reselect the cell

 

[Shadkng]

The original code allows me to click a cell which then brings up the comment box ready to type which is exactly what I want. What you suggested adds the comment to the cell but the box never opens. So my goal is to have the box close when I click away after I am done typing. If that is not possible then can the it be made to toggle the box open and close?

 

[Marc L]

Hi,​

​

to close try to set Visible to False at least …​

 

[Michael M]

Not near the computet at the moment. ..but you would need to use a worksheet selection change event

 

[rlv01]

I'm not super clear on what you want, but maybe something like this. It uses the double click event to add a new comment.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Cells.Count = 1 Then
        If Target.Comment Is Nothing Then
            addNewComment
        End If
    End If
End Sub

Sub addNewComment()
    ActiveCell.Interior.ColorIndex = 44                'interior color of selected cell
    ActiveCell.AddComment
    With ActiveCell.Comment
        With .Shape
            .AutoShapeType = msoShapeFoldedCorner
            .Fill.ForeColor.RGB = RGB(215, 224, 239)
            .Width = 150
            .Height = 75

            With .TextFrame
                .Characters.Font.Size = 11
                .Characters.Font.Name = "Calibri"
            End With
        End With
        .Visible = True
        .Shape.Select True
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim C As Comment

    For Each C In Me.Comments
        C.Visible = False
        C.Parent.Interior.ColorIndex = -4142
    Next C
End Sub

 

[Shadkng]

I'm not super clear on what you want, but maybe something like this. It uses the double click event to add a new comment.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Cells.Count = 1 Then
        If Target.Comment Is Nothing Then
            addNewComment
        End If
    End If
End Sub

Sub addNewComment()
    ActiveCell.Interior.ColorIndex = 44                'interior color of selected cell
    ActiveCell.AddComment
    With ActiveCell.Comment
        With .Shape
            .AutoShapeType = msoShapeFoldedCorner
            .Fill.ForeColor.RGB = RGB(215, 224, 239)
            .Width = 150
            .Height = 75

            With .TextFrame
                .Characters.Font.Size = 11
                .Characters.Font.Name = "Calibri"
            End With
        End With
        .Visible = True
        .Shape.Select True
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim C As Comment

    For Each C In Me.Comments
        C.Visible = False
        C.Parent.Interior.ColorIndex = -4142
    Next C
End Sub

Can you tell me where put the above code? Does it go in a module or in the sheet code? Thanks

 

[Michael M]

It goes in the Sheet module