Adding comment to 1 or more cells

Rip1971

New Member
Joined
Nov 3, 2020
Messages
49
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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.
 
Last edited:

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

nemmi69

Active Member
Joined
Mar 15, 2012
Messages
482
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Can you make use of this?

VBA Code:
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

New Member
Joined
Nov 3, 2020
Messages
49
Office Version
  1. 365
Platform
  1. Windows
Can you make use of this?

VBA Code:
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

Active Member
Joined
Mar 15, 2012
Messages
482
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Sorry, this needs to go at the top of the module page

'Create variables
Dim Rng As Range
Dim CommentTxt As String
 

Rip1971

New Member
Joined
Nov 3, 2020
Messages
49
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

VBA Code:
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

New Member
Joined
Nov 3, 2020
Messages
49
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,115
Office Version
  1. 2016
Platform
  1. Windows
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 :
VBA Code:
For Each oCell In Selection
    If Not (oCell.EntireRow.Hidden Or oCell.EntireColumn.Hidden) Then
        oCell.ClearComments
        oCell.AddComment Text:=strStatus
    End If
Next
 
Solution

Rip1971

New Member
Joined
Nov 3, 2020
Messages
49
Office Version
  1. 365
Platform
  1. Windows
How about this :
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,752
Messages
5,597,921
Members
414,190
Latest member
PuzzlerUK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top