Visable Character Limit / Scroll within Cell

[justin-uk]

I have a sheet with fixed row heights, but I want the user to be able to view everything in the row. There is likely to be up to 500 text characters in the cell.

I wanted a scroll bar affect, within the cell. Although it is not true scrolling I thought I could use the vertical align (top/center/bottom) to mimic a scroll.

I added a Form scroll bar to the cell and when activated the macro looks at the scroll bar value and changes the vertical alignment accordingly, so... (cell with scrollbar is D7, Link Cell with scrollbar value is B7)

Sub ScrollBar46_Change()
Select Case Range("B7").Value
Case 2
Range("D7").VerticalAlignment = xlBottom
Case 1
Range("D7").VerticalAlignment = xlCenter
Case 0
Range("D7").VerticalAlignment = xlTop
End Select
End Sub

That seems to work fine, however I have now realised that when aligning to xlBottom excel doesn't show the end of the text string, but only about 200 characters in.

I know there is a visable limit of 1,024 for Excel versions before 2007, but it's not this that is affecting my sheet. (this is discussed here http://exceltips.vitalnews.com/Pages/T003163_Character_Limits_for_Cells.html)

So
i) Can I get around this somehow by using my current method?
ii) If not, any ideas how I can create a scrolling affect in a cell that will show all of the data in that cell?

 

[Lewiy]

Perhaps include a commandbutton that opens up a UserForm with a label displaying the contents of the current cell:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

<o:p></o:p>

Private Sub UserForm_Activate()<o:p></o:p>
Label1.Caption = ActiveCell.Value<o:p></o:p>
End Sub<o:p></o:p>

<o></o>

 

[MickG]

Hi ,If you wrap your cell contents, by placing the cursor insertion point at the selected place in the cell string then press "Alt +Enter" the remaining text will be inserted on a new line within the cell.(Repeat for all of cell content)
If you then use a "List Box" or a "Forms Drop down Box" placed over that cell, you can List all the cell items in the Box by running either of these two codes.
It's not perfect but you certainly get the scrolling effect

Dim bit, c, al, ores()
ListBox3.Clear

For bit = 1 To Len([a2]) 'Step 4
    Do While Not Mid(Range("a2").Formula, bit, 1) = Chr(10)
        al = al & Mid(Range("a2").Formula, bit, 1)
        bit = bit + 1
    If bit = Len([a2]) + 1 Then Exit Do
        Loop
         c = c + 1
        ReDim Preserve ores(c)
        ores(c - 1) = al
        al = ""
Next bit

ListBox3.List = ores
ListBox3.ListIndex = 0

Dim bit As Integer, al As String

ActiveSheet.Shapes("List Box 7").ControlFormat.RemoveAllItems

For bit = 1 To Len([a4]) 
    Do While Not Mid(Range("a4").Formula, bit, 1) = Chr(10)
        al = al & Mid(Range("a4").Formula, bit, 1)
        bit = bit + 1
    If bit = Len([a4]) + 1 Then Exit Do
        Loop
ActiveSheet.Shapes("List Box 7").Select
Selection.AddItem al
al = ""
Next bit

Regards Mick