Comment Boxes + Freeze Panes = UGH!

Freeze Pane Problems With Comment Boxes

They Just Don't Mix Well

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

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
  • User must click on cell in order to see comment (cannot hover)
  • No indicator to easily show 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

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris "Macro" Newman :)