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.

 

[carpy1985]

Does this happen because the cell height expands?

If so would a macro to auto shrink all the rows to one "uniform" height help?

If so I'd make use of the Rows(1).RowHeight = 30 type function

 

[PivotMeThis]

Yes Carpy, the cell height expands. I like this idea of setting the row height, but could there also be a mechanism to expand the cell as needed. (not by double clicking between the rows) but by an arrow or something?

 

[PivotMeThis]

I think what I'm looking for is a scroll bar for each cell in the two columns that are causing me trouble. The length of the report changes from month to month. Is there a way for these to appear via VBA?

 

[PivotMeThis]

I just realized that I need to clarify how all this is working. I am very sorry that I didn't think about that in the first place.

This report contains a data sheet and multiple pivot tables. These pivot tables are set up with code and a sheet called "drill down" that formats the reports that are generated. I can easily set the column height with this. But if I set the column height the user will have to know how to resize the columns to read the entire description or reason and then I would be back to the field being too large. So I am thinking that if I could add a scroll bar to each of these cells in these two fields it would solve the problem.

Any ideas?
Thanks

 

[Jerry Sullivan]

Hi again Rhonda,

Excel doesn't have the functionality to add scroll bars directly to the Cell. It's possible to add a Textbox on top of the Cell that would emulate this, but that might be problematic.

Perhaps you could use a temporary Comment box that would pop up when a cell is double-clicked.

Since it would typically show the entire contents of even very long notes, that might be a nicer interface than a scroll bar.

If this works for you, then you could use a fixed row height with no word wrap.

Below is some code you can try.

If you copy and paste this in the ThisWorkbook Module, it will add this double-click functionality to all worksheets in the workbook. The previous temporary comment is deleted each time a new cell is double-clicked and it is also deleted before each Save.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _
        ByVal Target As Range, Cancel As Boolean)
    
    Dim lArea As Long
    Dim sText As String
    
    Const lWidth As Long = 200 'Width of Comment Shape
    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 Target 
        .Cells(2).Select
        .AddComment
        With .Comment
            .Visible = True
            .Text Text:=.Parent.Text
            .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
        End With
    End With
    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()
[COLOR="#008080"]'---clear previous temp comment, if any[/COLOR]
    On Error Resume Next
    With Names("PreviousCell.wTempComment").RefersToRange
        Debug.Print .Address
        If Not .Comment Is Nothing Then .Comment.Delete
    End With
End Sub

Please let me know your thoughts. It can be tweaked as needed.

 

[PivotMeThis]

Thanks Jerry! I justed wanted to let you know I got this and will try it out as soon as I can and get back to you. I was out of the office late last week and came back this morning to one of my other reports blowing up in my face. (works on my machine but nobody elses) I need to try and figure that out before I can come back to this.

 

[PivotMeThis]

Oh Jerry, this is fantastic!

The only thing I'd like to change is that these comments sometimes have "carriage returns" (enter, enter) and the comment box shows a small square box each time there is one in a comment.

Well, no, I guess I do have a couple more questions...

One column of these "reasons" is in the last column so the text runs on out over the rows to the right.

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.

Any way to fix these items?
Thanks!

 

[Jerry Sullivan]

For the carriage returns, try modifying this line to read...

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

The comment position is a good idea. It can be done, but I won't be able to get to that until tonight.

 

[PivotMeThis]

That works great.

I thought I had a solution for the overflow on the text but it only works on my data sheet and not the drill down sheets. This is from an old thread I stumbled over.

Sub Macro1()
For i = 1 To 20000
If Not IsEmpty(Cells(i, 43)) And IsEmpty(Cells(i, 44)) Then
Cells(i, 44) = " "
End If
Next

End Sub

I'm going to keep playing with it and see if I can make it work.

Thanks Jerry, I look forward to tomorrow