Adding comment to 1 or more cells

[Rip1971]

Hi,

I am looking for a piece of code that more or less follows this proces. it is to show a status change of userid which is in this cell for further filtering

- select 1 or more cells. click on a button this starts below code. this is a simply color cell code to.
- Add comment to all selected cells with the the current date at that comment
- if there is already a comment in the cell i want to change the comment to the current date at that moment.

Sub Voorinschrijving(control As IRibbonControl)
'
' Voorinschrijving Macro
'

'
Sheets("Data").Select
ActiveCell.Select
ActiveCell.AddCommentThreaded ("Gepland op:")
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

who can help me with this.

 

[nemmi69]

Can you make use of this?

Sub CellComment()

'Set variables
Set Rng = ActiveCell

'Try to set variable. If error then comment does not exist
On Error Resume Next
CommentTxt = Rng.Comment.Text
On Error GoTo 0

'If comment exists then display message box
If CommentTxt <> "" Then
    MsgBox "Cell comment : " & CommentTxt
End If

'New replacement text
CommentTxt = "Some new text"
Rng.Comment.Text Text:=CommentTxt
End Sub

Sub ClearComment()
'Set variables
Set Rng = ActiveCell

'Clear comments from range
Rng.ClearComments

End Sub

 

[Rip1971]

Can you make use of this?

Sub CellComment()

'Set variables
Set Rng = ActiveCell

'Try to set variable. If error then comment does not exist
On Error Resume Next
CommentTxt = Rng.Comment.Text
On Error GoTo 0

'If comment exists then display message box
If CommentTxt <> "" Then
    MsgBox "Cell comment : " & CommentTxt
End If

'New replacement text
CommentTxt = "Some new text"
Rng.Comment.Text Text:=CommentTxt
End Sub

Sub ClearComment()
'Set variables
Set Rng = ActiveCell

'Clear comments from range
Rng.ClearComments

End Sub

I get an error in the line 'Rng.Comment.Text Text:=CommentTxt' so right now i cannot get it to work

 

[nemmi69]

Sorry, this needs to go at the top of the module page

'Create variables
Dim Rng As Range
Dim CommentTxt As String

 

[Rip1971]

Sorry, this needs to go at the top of the module page

'Create variables
Dim Rng As Range
Dim CommentTxt As String

I am sorry to say this but the code still gives an error even after adding these lines.

i have been playing with simple lines and this almost does what it should. There is one problem if i select cells where there are hidden rows in between it also adds a comment to those row. This however does not happen with the formating of the cells. how can i adjust this so only active cells are getting a comment.

Sub AddComment()
'
' Voorinschrijving Macro
'

'
Sheets("Data").Select
Dim oCell As Range
Dim strStatus As String
Dim LDate As String
Dim PlDate As String
Dim Rng As Range

LDate = Date
' PlDate = Application.InputBox("Geeft de geplande datum voor: ", Title:="Geplande Datum")
If PlDate = "" Then
PlDate = LDate
End If
strStatus = "Uitgenodigd " + PlDate

For Each oCell In Selection
oCell.ClearComments
oCell.AddComment Text:=strStatus
Next

'With Selection.Interior
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
' .Color = 15773696
' .TintAndShade = 0
' .PatternTintAndShade = 0
'End With
End Sub

 

[Rip1971]

I am sorry to say this but the code still gives an error even after adding these lines.

i have been playing with simple lines and this almost does what it should. There is one problem if i select cells where there are hidden rows in between it also adds a comment to those row. This however does not happen with the formating of the cells. how can i adjust this so only active cells are getting a comment.

Sub AddComment()
'
' Voorinschrijving Macro
'

'
Sheets("Data").Select
Dim oCell As Range
Dim strStatus As String
Dim LDate As String
Dim PlDate As String
Dim Rng As Range

LDate = Date
' PlDate = Application.InputBox("Geeft de geplande datum voor: ", Title:="Geplande Datum")
If PlDate = "" Then
PlDate = LDate
End If
strStatus = "Uitgenodigd " + PlDate

For Each oCell In Selection
oCell.ClearComments
oCell.AddComment Text:=strStatus
Next

With Selection.Interior
 .Pattern = xlSolid
 .PatternColorIndex = xlAutomatic
 .Color = 15773696
 .TintAndShade = 0
 .PatternTintAndShade = 0
End With
End Sub

Who can help me to fix this code so it only adds a comment to the visible cells and not to the hidden cells

 

[Jaafar Tribak]

Who can help me to fix this code so it only adds a comment to the visible cells and not to the hidden cells

How about this :

For Each oCell In Selection
    If Not (oCell.EntireRow.Hidden Or oCell.EntireColumn.Hidden) Then
        oCell.ClearComments
        oCell.AddComment Text:=strStatus
    End If
Next

 

[Rip1971]

How about this :

For Each oCell In Selection
    If Not (oCell.EntireRow.Hidden Or oCell.EntireColumn.Hidden) Then
        oCell.ClearComments
        oCell.AddComment Text:=strStatus
    End If
Next

thank you this does the trick