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

eabaker64

New Member
Joined
Jul 1, 2024
Messages
35
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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.

 
Upvote 0
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.
Code:
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)
Code:
Sub Delete_TextBox()
    ActiveSheet.Shapes("TB1").Delete
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,885
Messages
6,181,576
Members
453,055
Latest member
cope7895

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top