Comment in wrong cell

Topic

New Reply

The following code inserts a comment with time, date, and cell value when a cell is changed. It works fine when the user presses Enter to enter the cell value, but if instead of pressing Enter the user selects a new cell the value is entered alright in the previous cell but the comment appears in the new cell. I hoped declaring rowInt and colInt as Static would cure this but it did not. So how can I assign values to rowInt and colInt so they do not change when the activecell is changed?

TIA

Sub InsertComment()
Static rowInt As Integer
Static colInt As Integer
Dim NumCells As Integer
Application.MoveAfterReturn = False
NumCells = Selection.Cells.Count
If NumCells 1 Then
MsgBox (“Macros only work with 1 cell selected”): Exit Sub
Else
rowInt = ActiveCell.Row
colInt = ActiveCell.Column
Application.ActiveSheet.Cells(rowInt, colInt).AddComment
Application.ActiveSheet.Cells(rowInt, colInt).Comment.Visible = True
Application.ActiveSheet.Cells(rowInt, colInt).Comment.Shape.Width = 250
Application.ActiveSheet.Cells(rowInt, colInt).Comment.Text “Comment inserted ” & Time & ” ” & Date _
& ” ” & Cells(rowInt, colInt).Value & Chr(10), 1, False
End If
End Sub

Reply | Quote

Replies

The following code inserts a comment with time, date, and cell value when a cell is changed. It works fine when the user presses Enter to enter the cell value, but if instead of pressing Enter the user selects a new cell the value is entered alright in the previous cell but the comment appears in the new cell. I hoped declaring rowInt and colInt as Static would cure this but it did not. So how can I assign values to rowInt and colInt so they do not change when the activecell is changed?

TIA

It seem to me that you should be using the Worksheet_Change event on each sheet fire your macro. Passing the Target properties to a macro in a standard module should do the trick.

Reply | Quote

It seem to me that you should be using the Worksheet_Change event on each sheet fire your macro. Passing the Target properties to a macro in a standard module should do the trick.

My worksheet does have a Worksheet_change event to fire the macro (or a comment update macro for a cell that already has a comment), that part works fine; I did not show that macro as it is working as intended. The only problem is if the user enters new data in the cell then, without pressing Enter, uses the mouse to select another cell , the data appears in the cell that was changed alright but the comment appears in the newly selected cell. I think this must be because rowInt and colInt are set by reference to the ActiveCell, what I need is a way of setting rowInt and colInt so they refer to the ActiveCell at the start of the macro and do not alter when a new ActiveCell is selected.

Reply | Quote

Hans has shown you how to do this.

DO NOT use the Active Cell, use the Target range passed to your procedure.

Reply | Quote

The following code inserts a comment with time, date, and cell value when a cell is changed.

If you want to insert a comment whenever the value of a cell in any worksheet is changed, use the Workbook_SheetChange event in the ThisWorkbook module. Here is an example that should soon drive any user mad:

Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Dim oCell As Range
  On Error GoTo ErrHandler
  Application.EnableEvents = False
  For Each oCell In Target.Cells
	On Error Resume Next
	oCell.AddComment
	On Error GoTo ErrHandler
	oCell.Comment.Shape.Width = 250
	oCell.Comment.Text "Comment inserted " & Now & vbLf, 1, False
  Next oCell

ExitHandler:
  Application.EnableEvents = True
  Exit Sub

ErrHandler:
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
End Sub

Reply | Quote