I am not experienced with ScrollHeight so I need help on this.
I am creating a dynamic form to display a lot of text to the end user. There will be so much text that I will need a scroll bar to make navigation obvious/easily accessible to end users. Now I have have the text displayed on a control * which sits just inside a Frame that contains a working scrollbar (thanks to code by Andy Pope)
Unfortunately the scrollbar movement is usually too small depending on the amount of text being passed.
I need a formula that will evaluate the size of text passed (should I do this by linebreaks or len?) and then use this to calculate an appropriate Scrollheight so that the end result is that the scroll bar top/bottom = the top/bottom of the text displayed inside the textbox inside the frame.
* I started off using a Label but was forced to adopt a TextBox to bypass the character limits on Labels
P.S. I didn't think this would be needed but if it helps solve this thread faster, below is the code in the form I am creating. I have used red font where I am stumped (If anybody wants to recreate this form - place a textbox inside a frame on a new userform. The code will dynamically set size & position of the frame & textbox)
Option Explicit
Private mvarText As Variant
Private mblnFormFakeInitialize As Boolean
Public Property Let FormText(ByVal varText As Variant)
mvarText = varText
End Property
Private Sub UserForm_Activate()
' necessary due to Form Initializing before properties passed
If Not mblnFormFakeInitialize Then
Call pFormFakeInitialize
mblnFormFakeInitialize = True
End If
End Sub
Private Sub pFormFakeInitialize()
Const lngcGAP As Long = 6
Dim lng As Long
With Me
' dynamic size & position
' Frame to almost fill the entire form
.Frame1.Left = lngcGAP
.Frame1.Width = .InsideWidth - (lngcGAP * 2)
.Frame1.Top = lngcGAP
.Frame1.Height = .InsideHeight - (lngcGAP * 2)
'With .Label1
With .TextBox1
' form object to fill Frame
.Left = 0
.Top = 0
.Width = Me.Frame1.InsideWidth
.Height = Me.Frame1.InsideHeight
' make TextBox look like a Label
.BorderStyle = fmBorderStyleNone
.Locked = True
.WordWrap = True
.MultiLine = True
End With
' load string to form object
'.Label1.Caption = mvarText
.TextBox1.Text = mvarText
'with .Label1
With .TextBox1
.Height = .Height + lngcGAP
.SelLength = 1
End With
With .Frame1
.Caption = ""
'lng = Me.Label1.Top + Me.Label1.Height + lngcGAP
lng = Me.TextBox1.Top + Me.TextBox1.Height + lngcGAP
.ScrollHeight = lng
.ScrollBars = fmScrollBarsVertical
End With
End With
End Sub
Bookmarks