Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='Michael Evans' post='762957' date='02-Mar-2009 19:35']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[/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.
    Regards
    Don

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Michael Evans' post='762957' date='03-Mar-2009 01:35']The following code inserts a comment with time, date, and cell value when a cell is changed.[/quote]
    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

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='762970' date='03-Mar-2009 02:39']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.[/quote]

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='Michael Evans' post='762974' date='03-Mar-2009 02:11'][/quote]
    Hans has shown you how to do this.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •