×

How To Resolve Cell Notes/Freeze Panes Issues

By Chris Newman •  Updated: 02/18/14 •  4 min read
How To Resolve Cell Notes/Freeze Panes Issues

They Just Don’t Mix Well

What do you get when you put a cell Note Box right next to a freeze pane line?  A hot, stinky mess!  Sometimes the comment box gets resized, and sometimes only half of the box is readable…but whatever Excel tries to do with this combination, it looks extremely ugly and unprofessional!

Freeze Pane Comment Box Problem and Solution in Excel

So What’s The Workaround?

The main workaround I use to solve this is to use Excel’s Data Validation feature.  There is a character limit of 255 characters so this can’t be used in every scenario but if you are using the comment box for simple reminders this is a good solution.

Let’s say I want to add a comment box to Cell D2 to describe where I need to pull that data from.

Freeze Pane Comment Box Problem and Solution in Excel

I first need to highlight the cell(s) that I want to trigger the comment to appear when a user clicks in one.  Then go up to the Ribbon and select Data > Data Validation > Data Validation…

Freeze Pane Comment Box Problem and Solution in Excel

The Data Validation dialog box should now appear.  In the Settings tab make sure that the ‘Allow’ criteria is set to Any Value.

Freeze Pane Comment Box Problem and Solution in Excel

Then go into the Input Message tab and type in a Title and Message for what you want your comment box to say.  Also make sure the Show input message when cell is selected checkbox is checked.

Data Validation Input Message

Click OK and when you select the cell(s) you put the Data Validation on you should see a pale yellow floating box appear below the cell.  This box always appears on top of freeze panes so your users can always read your message.

Freeze Pane Comment Box Problem and Solution in Excel

Downsides To This Method

There are a few restrictions while using this method:

  • Max amount of characters is 255
  • The user must click on a cell in order to see a comment (cannot hover)
  • No indicator to easily show the user that there is a comment on a specific cell
  • No customization to the comment box

Mimicking The Comment Box Indicator (Red Flag)

Below is the VBA macro I use to add a little red flag to my cells to notify me that there is a comment box on that cell.  The code basically just creates a small red triangle shape and positions it in the top right-hand corner of your selected cell.  This code was inspired by a great Contextures Blog post on VBA and Comment Boxes.

Sub CreateCommentIndicator()

'PURPOSE: Add Comment Box-like Red Indicator in Selected Cell
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim myIndicator As Shape
Dim shp_Width As Double
Dim shp_Height As Double

Set sht = ActiveSheet
shp_Width = 6
shp_Height = 4

'Create Shape
    Set myIndicator = sht.Shapes.AddShape(msoShapeRightTriangle, _
        ActiveCell.Offset(0, 1).Left - shp_Width, _
        ActiveCell.Top, shp_Width, shp_Height)

'Reposition Shape and Change Color to Red
  With myIndicator
    .Flip msoFlipVertical
    .Flip msoFlipHorizontal
    .Fill.ForeColor.RGB = RGB(255, 0, 0)
    .Line.Visible = msoFalse
  End With

End Sub

The Finished Product

Freeze Pane Comment Box Problem and Solution in Excel

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.