Textbox/comment box to show cell contents when cell is double clicked

[eabaker64]

Hi. My spreadsheet has merged cells in it that I cannot AutoFit the height on, which is okay because if the work-around is done, then adjacent cells in the same row end up differently than I want. Is there a way an individual could double click on cells (for example: A13:D13 - the merged cells), and a text box or some type of box would come up and show the entire contents of the cell? Sometimes the information in the merged cells may be paragraph form and fairly long. I know right now if you double click the cells, it does show you everything in that cell, but it is sometimes hard to read, and I thought it would stand out better if it had a "box" that it appeared in. Any help is greatly appreciated.

 

[jolivanes]

You're best way to go is to get rid of the merged cells. There are quite a few articles on the web that explain why that is and also the alternatives.
They will, as you now see, bite you in the behind at one time or another.
Have a read of some of these articles and if there is absolutely no other way, come back here and let us know.

Excel Tips: Why You Should Avoid Merging Cells

Use the Center Across Selection feature to easily merge cells in Excel.

edu.gcfglobal.org

Stop! Do Not Merge Cells in Excel - Heres why with fixes - The Excel Club

Find out why you should not merge cells in excel, the problems merged cells can cause and how you can overcome this problem.

theexcelclub.com

Don't merge cells in Excel // Best practices // PerfectXL Academy

It's very tempting to merge cells in Excel, but try to resist the temptation because merging cells can be dangerous!

www.perfectxl.com

Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

www.atlaspm.com

 

[jolivanes]

If you cannot get away from the merged cells.
In the Sheet Module that has the merged cells. (Experiment with the TextBox sizes)
Change references (sheet names etc.) as required.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim s As Shape
    If Not Intersect(Target, Range("A13")) Is Nothing Then
        Cancel = True
        Set s = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 10, 10, 300, 300)    '<------ Change as required
            s.Name = "TB1"
            s.TextFrame.Characters.Text = Sheets("Sheet1").Range("A13").Text
'           s.TextFrame.AutoSize = True    '<---- Uncomment this line if you want
    End If
End Sub

In a regular module (to delete the TextBox)

Sub Delete_TextBox()
    ActiveSheet.Shapes("TB1").Delete
End Sub