Positioning comments on an Excel worksheet Microsoft.Office.Interop.Excel / C#

I wish to position comments on top of the target cell but they are always placed at the same (default) position, with the respect of the target cell. I have tried increasing the shape.Top and shape.Left every other comment but nothing. Any suggestion?

Here’s the function:

private static void AddComments(Worksheet worksheet, Range sourceRange, Range targetRange)
{
        int lastRow = GetLastRowInColumn(worksheet, targetRange.Column);

        int sourceColumn = sourceRange.Column;
        int targetColumn = targetRange.Column;

        int[] missingCommentRows = new int[0];

        for (int i = targetRange.Row + 1; i <= lastRow; i++)
        {
            targetRange = worksheet.Cells[i, targetColumn];
            sourceRange = worksheet.Cells[i, sourceColumn];

            // Clear comment, if any
            targetRange.ClearComments();

            string sourceValue = sourceRange.Value2.ToString().Trim();
            if (!string.IsNullOrEmpty(sourceValue))
            {
                sourceValue = char.ToUpper(sourceValue[0]) + sourceValue.Substring(1);
                if (sourceValue.EndsWith("."))
                {
                    _ = sourceValue.Substring(0, sourceValue.Length - 1);
                }

                Comment comment = targetRange.AddComment();
                Shape shape = comment.Shape;

                // Formatting
                shape.TextFrame.Characters().Font.Size = 9;
                shape.TextFrame.Characters().Font.Name = "Arial";
                shape.TextFrame.Characters().Font.Bold = false;
                shape.TextFrame.Characters().Font.Italic = true;
                shape.TextFrame.Characters().Font.Color = Color.Red;
                shape.TextFrame.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;

                // Sizing
                shape.TextFrame.AutoSize = true;

                // Positioning
                float topDistance = shape.Top;
                float leftDistance = shape.Left;
                if (i % 2 == 0)
                {
                    topDistance += 20;
                    leftDistance += 20;
                }
                shape.Top = topDistance;
                shape.Left = leftDistance;
                shape.Top = (int)targetRange.Top + 100;
                shape.Left = (int)targetRange.Left + 100;

                // Name
                shape.Name = "cmnt_" + targetRange.Address;

                comment.Text(sourceValue);
                comment.Visible = false;
            }
            else
            {
                // Create a new array with increased size to hold row number where the comment is missing
                int[] newArray = new int[missingCommentRows.Length + 1];

                // Copy the existing elements to the new array
                Array.Copy(missingCommentRows, newArray, missingCommentRows.Length);

                // Add row number to the new array
                newArray[newArray.Length - 1] = i;

                // Update the reference to the array
                missingCommentRows = newArray;
            }
        }

        // Delete the entire column
        sourceRange.EntireColumn.Delete();
}

Many thanks!

Leave a Comment