Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    automatic comment (Excel 2000)

    could we make comment automatically comes up if specified cell met some criteria? instead of make some remark in next cell, with "if" formulae
    any thought are appreciated.

    indra

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: automatic comment (Excel 2000)

    Is the value of the specified cell entered by the user, or does it contain a formula.
    If you could provide an example of what you want, it would be easier to help you.

  3. #3
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automatic comment (Excel 2000)

    It is contain formula,
    attached the file, cell with circle, and remark suppose to appear as comment not in different cell

    indra
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: automatic comment (Excel 2000)

    You could use code in the Worksheet_Calculate event of the worksheet. I'm not sure this is a good idea - it will slow down performance, and it will disable undo.

    The code in the worksheet module would look like this:

    Private Sub Worksheet_Calculate()
    SetComment Range("M21")
    SetComment Range("M26")
    SetComment Range("M33")
    SetComment Range("M40")
    SetComment Range("M51")
    SetComment Range("M60")
    End Sub

    In a standard module (the kind you create by selecting Insert | Module in the Visual Basic Editor), create this procedure:

    Sub SetComment(oCell As Range)
    On Error Resume Next
    oCell.ClearComments
    If oCell > 1.1 Then
    oCell.AddComment "Please contact Yogya"
    End If
    End Sub

    See attached version.
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automatic comment (Excel 2000)

    Thanks Hans,
    anyway, if I want to modify this VBA code, regarding different of selected cell, could you explain a bit?

    regards

    indra

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: automatic comment (Excel 2000)

    The Worksheet_Calculate event procedure in the worksheet module calls the SetComment procedure for each cell in which you want to set a condtional comment. You can modify the cell addresses, and add or remove lines as needed.

    The SetComment procedure starts by removing existing comments, then it checks the value of the cell, and if this is larger than 1.1 (=110%), it sets a new comment. You can change the condition as needed, as well as the text of the comment.

Posting Permissions

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