• automatic comment (Excel 2000)

    Author
    Topic
    #441663

    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

    Viewing 0 reply threads
    Author
    Replies
    • #1061113

      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.

      • #1061136

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

        indra

        • #1061139

          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.

          • #1061146

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

            regards

            indra

            • #1061149

              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.

    Viewing 0 reply threads
    Reply To: automatic comment (Excel 2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: