Visable Character Limit / Scroll within Cell

justin-uk

New Member
Joined
Dec 3, 2007
Messages
21
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?
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
Rich (BB code):
<o:p></o:p>
Rich (BB code):
Rich (BB code):
Private Sub UserForm_Activate()<o:p></o:p>
Label1.Caption = ActiveCell.Value<o:p></o:p>
End Sub<o:p></o:p>
<o:p></o:p>
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
Code:
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
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,824
Messages
5,446,719
Members
405,414
Latest member
wayne_p

This Week's Hot Topics

Top