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!
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.
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…
The Data Validation dialog box should now appear. In the Settings tab make sure that the ‘Allow’ criteria is set to Any Value.
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.
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.
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 SubThe Finished Product
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
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.