Collapse cell

joacro

Board Regular
Joined
Jun 24, 2010
Messages
158
Hi everyone

I was wondering if anyone can assist me with collapsing a cell which is to long. I see that it has been done before but I am new to vba and would like more specific coding to apply it to column AK

Would it be possible to help me with this.

I have a long column where we insert comments on interactions with clients. This makes the spreadsheet uncomfortable.

I need the column to collapse same as here. Please see link: http://www.mrexcel.com/forum/excel-questions/726836-possible-collapse-cell-long-text.html


Hope someone will be able to assist me

Regards

Joacro
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Steph77

Board Regular
Joined
Sep 18, 2014
Messages
119
The code in the linked thread doesn't care what column the long data is in, you double click anywhere in a row and it will autoexpand to the highest column height needed for whatever the longest cell in that row is, double click again and it will collapse it to just one line.

If you want something different to that can you be more specific about how what you want differs from this.
 

joacro

Board Regular
Joined
Jun 24, 2010
Messages
158
Hi there, Steph77,

I used the following code from Jerry:


Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) Dim lArea As Long, lRightGrid As Long Dim sText As String Const lWidth As Long = 200 'Width of Comment Shape sText = Target.Text Clear_Temp_Comment '--Only add comment for cells with text wider than column If Len(sText) < Target.ColumnWidth Then Exit Sub '--Don't overwrite existing comments If Not Target.Comment Is Nothing Then Exit Sub With ActiveWindow.VisibleRange '---get position of rightmost visible gridline lRightGrid = .Offset(0, .Columns.Count).Left End With With Target On Error Resume Next If Not IsError(Target.PivotCell.PivotCellType) Then Cancel = True .AddComment With .Comment .Visible = True .Text Text:=Replace(Replace(.Parent.Text, Chr(10), " "), Chr(13), " ") .Shape.TextFrame.AutoSize = True If .Shape.Width > lWidth Then lArea = .Shape.Width * .Shape.Height .Shape.Width = lWidth .Shape.Height = (lArea / lWidth) * 1.2 End If If lRightGrid - .Shape.Left < lWidth + 50 Then '--comment won't fit to right of Target .Shape.Left = Application.Max(0, _ Application.Min(lRightGrid - lWidth - 5, _ .Shape.Left - lWidth - 25)) .Shape.Top = .Shape.Top + 25 End If End With End If End With ActiveWorkbook.Names.Add "PreviousCell.wTempComment", RefersTo:=Target, Visible:=False End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Clear_Temp_Comment End Sub Private Sub Clear_Temp_Comment() '---clear previous temp comment, if any On Error Resume Next With ActiveWorkbook.Names("PreviousCell.wTempComment") With .RefersToRange If Not .Comment Is Nothing Then .Comment.Delete End With .Delete End With End Sub

It works perfectly,

I just wanted to know how I can get it more organized. It just place everything into a comment box as one long string.

Is it possible to perhaps say after a "." to go to the next line so that it will look more organized

Hope to hear from you soon.

Regards
</pre>
 

Watch MrExcel Video

Forum statistics

Threads
1,123,331
Messages
5,601,006
Members
414,420
Latest member
Richie77777

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
Top