Need solution for fields so large they are causing scrolling problems

[PivotMeThis]

I receive a report from another office which contains "description of changes" and "reasons for contract changes". I use index and match to add this information to the list of contract changes. This is the only way we can get this data. The problem is that the information is sometimes so long that scrolling down the page becomes a problem because you can't tell where you are. I might be on line 278 and scroll down and suddenly I'm on 295. That probably doesn't make any sense but you can't see the end of the cell most of the time and when you finally do, you've scrolled past a lot of data. This is very frustrating and I'm wondering if anyone has a solution to roll this info up until it's needed or hide some of it or something. It all resides on one sheet but is brought into 3 other sheets via index and match.

I appreciate any ideas anyone might have. I'm at a complete loss.

 

[PivotMeThis]

After playing around with this macro and giving it some additional thought I realize that it won't work with the drill down tables because the pivot tables did not take that column into consideration. If i change my data source to include this column with the blank space my drill down tables have a new blank column and if I hide that with the drill down formatting the text overflows again. Not such a brilliant idea after all...I took it out of the code and back to looking for another option.

 

[PivotMeThis]

Jerry, I found a solution to the text overflow in the drilldown sheets.

I used .HorizontalAlignment = xlFill in the drill down module and that seems to have taken care of it.

 

[Jerry Sullivan]

Also, is there a way to "place" the comment box in a certain position? When the last cell to the right is double clicked the comment box is to the right of that so the user has to scroll over to see it.

Here is modified code you could try to address the problem of the comment not being visible if placed the right of the rightmost column.

Replace the previous Workbook_SheetBeforeDoubleClick Sub with the code below. All the other procedures remain unchanged.

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 [COLOR="#008080"]'Width of Comment Shape[/COLOR]
    sText = Target.Text

    
    Clear_Temp_Comment
 
   
[COLOR="#008080"]    '--Only add comment for cells with text wider than column[/COLOR]
    If Len(sText) < Target.ColumnWidth Then Exit Sub
 
   
 [COLOR="#008080"]   '--Don't overwrite existing comments[/COLOR]
    If Not Target.Comment Is Nothing Then Exit Sub

    With ActiveWindow.VisibleRange
[COLOR="#008080"]        '---get position of rightmost visible gridline[/COLOR]
        lRightGrid = .Offset(0, .Columns.Count).Left
    End With

    With Target
        .Cells(2).Select
        .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
[COLOR="#008080"]            '--comment won't fit to right of Target[/COLOR]
                .Shape.Left = Application.Max(0, _
                    Application.Min(lRightGrid - lWidth - 5, _
                    .Shape.Left - lWidth - 25))
                .Shape.Top = .Shape.Top + 25
            End If
        End With
    End With
    Names.Add "PreviousCell.wTempComment", RefersTo:=Target, Visible:=False
 End Sub

This will place the comment just below and to the left of the upper-right corner of the cell with the long text string. It works pretty well when the entire cell is visible.

If the cell with the long text is only partially shown, it will scroll to show the cell. This isn't ideal, but better than alternatives I considered. Let me know if that works for you.

 

[Jerry Sullivan]

Jerry, I found a solution to the text overflow in the drilldown sheets.

I used .HorizontalAlignment = xlFill in the drill down module and that seems to have taken care of it.

It's great that you discovered this workaround. This looks like it works well for longer strings of text; however it will pad shorter strings of text with spaces to Left and Right justify them. If that's a problem, two alternatives might be:

1. Following the approach of the macro you found, place spaces or another character in the column to the right of your drill down data. This wouldn't have to be part of your source data as you were thinking. It could just be added as the last step of your drill-down formatting macro that we developed.

2. Apply WrapText to the column and apply fixed height to the rows.

 

[PivotMeThis]

This code works great Jerry! Thanks You are truly the BEST!!!

I see that the text.text is removing carriage returns and new lines. For readability, I want to keep the new lines but when I tried to remove the section Chr(10), " "), the code wouldn't work. As a work around I changed the 10 to 13. What should I do to make it correct?

On the drill down - you are right that my solution didn't work so great for the shorter strings, so I applied the WrapText to the columns and RowHeight to the rows. This makes the report look very nice. The big shots should love it.

 

[PivotMeThis]

Well, I guess there is one more thing. Sometimes the comments are hanging up and not closing properly. Not sure what to do about that. Double clicking on something else is not always effective. Is there a way to single click to close or something like that?

 

[Jerry Sullivan]

Hi Rhonda,

You could use this to removed carriage returns but not newlines.

  .Text Text:=Replace(.Parent.Text, Chr(13), " ")

Could you clarify what you mean by "Sometimes the comments are hanging up and not closing properly"
As designed, the comment should be visible until:
1. Another cell is double-clicked
2. The file is saved
3. The comment is manually deleted or not displayed by the user (ie Right-Click on cell > Delete Comment).

I initially experimented with ...

With .Comment
   .Visible = False

...which only shows the comment when the user hovers the cursor over the cell.
I thought this was more difficult to use, but you might prefer it.

 

[PivotMeThis]

Hey Jerry, thanks for the change in code to remove only the carriage returns.

What I meant by hanging up...sometimes when you double click on a cell, a comment box opens and then when you double click on another cell the original comment box does not close all the way so you have 2 comment boxes open. Or one and a part of another overlapping. It's really inconsistent and maybe I'm making selections too quickly. I have watched what cells I'm clicking when it happens and can't duplicate it when clicking the same cells. If I double click in a new cell the first comment remains open, the second closes and the third opens. I can keep doing this and eventually it will close. Or close the worksheet and come back to it that works too.

Because the cursor jumps to the next cell when you double click; I don't want to tell anybody to right click and delete comments, I'm afraid someone may delete more than just comments. I don't think the hover idea would work very good for some people either but I'll test is because it sounds pretty cool! ...you're right, it IS hard to use.

 

[Jerry Sullivan]

Now, I see what you mean. The problem was due to the Name that saves the reference to the previous cell with comment not being deleted, in which case a Name with Worksheet scope was created instead.

Replace all the previous code in the ThisWorkbook Module with these modified procedures.

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

This revision also elminates the selecting of the cell below. That was being used to prevent the Double-Click from entering the Cell for editing.

Thanks to an example TusharM, posted yesterday, I learned that a Cancel = True statement is a better way to do that.

 

[PivotMeThis]

Thank you Jerry, this code works really slick! The only thing I changed was the ".text text" line so that it could keep the new lines.

I really appreciate all your help. This code is already in 5 of my workbooks and bound to end up in more. I love the way your code is so flexible and easy for me to understand.

As always - you are the BEST!!!