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.
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
- 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.
'PURPOSE: Add Comment Box-like Red Indicator in Selected Cell
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
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
.Fill.ForeColor.RGB = RGB(255, 0, 0)
.Line.Visible = msoFalse
The Finished Product
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 :)