VBA to scroll down a userform textbox (line by line)?

Question

As noted in thisprevious post, I've been unable to get userform textboxes to auto-scroll to the bottom. As best I can tell, this looks like a bug. That previous post contains sufficient information to replicate the problem. Using .CurLine=-1 also does not solve the problem.

I need to move this project forward, so I'm hoping for a workaround but [don't know/can't find] the appropriate object model reference.

Instead of (or in addition to) using

UF1.TextBox4.SelStart = Len(UF1.TextBox4.Text)

I'd like to use code to manually scroll down the textbox. I figure I can just do this 2-3 times, and thereby force it to show the last row of text.

Does anyone know the appropriate command, something like

UF1.Textbox4.Scrolldown(1)

or anything that would mimic clicking on the downarrow on the vertical scrollbar?

Thank you!!

Answer 1

I have done a lot of testing with this today and found the following.

SetFocus must be before using SelStart, SelLength and SelText. (Makes sense to me because they refer to "Selected" and the control must have focus to select within the control.)

Insert DoEvents immediatlely after SetFocus to ensure that the control has focus before setting SelStart to select the position.

I am not able to fault the code below with extensive testing so interested to see if your testing can fault. In actual fact, when clicking the scroll arrow, the text moves down about half a line. (I included 2 options for the Activate Event to position the cursor and visible data.)

I have commented out the code in the Initialize event for positioning the cursor because I am satisfied that does not work because the Userform is not displayed during initialize.

I have left commented out code for creating the stings with and without linefeeds so suggest testing with the various options. 

Private Sub UserForm_Activate()       'Option 1 for Activate Event

   Dim Item

   For Each Item In Array(Me.TextBox1, Me.TextBox2)

        With Item

            .SetFocus

            DoEvents

            .CurLine = .LineCount - 1

        End With

     Next Item

End Sub

Private Sub UserForm_Activate()      'Option 2 for Activate Event

   Dim Item

   For Each Item In Array(Me.TextBox1, Me.TextBox2)

        With Item

            .SetFocus

            DoEvents

            .SelStart = Len(.Value)

        End With

     Next Item

End Sub

Private Sub UserForm_Initialize()

    Dim Item

    Dim i As Integer

    Dim s As String

    For Each Item In Array(Me.TextBox1, Me.TextBox2)

        With Item

            .MultiLine = True

            .ScrollBars = fmScrollBarsBoth

            '.ScrollBars = fmScrollBarsVertical

            .WordWrap = True

            .Locked = True

            .EnterFieldBehavior = fmEnterFieldBehaviorRecallSelection

            For i = 1 To Rnd * 15 + 25

                'Only uncomment one of the following at a time for testing with and without linefeeds

                s = s & String(Rnd * 20 + 1, Chr(64 + i)) & " "

                's = s & String(Rnd * 20 + 1, Chr(64 + i)) & " " & vbLf      'Chr(10) only

                's = s & String(Rnd * 20 + 1, Chr(64 + i)) & " " & vbCr      'Chr(13) only

                's = s & String(Rnd * 20 + 1, Chr(64 + i)) & " " & vbCrLf    'Chr(10) and Chr(13)

            Next

            .Value = s

            'Repeated failures attempting the following code before the Userform is displayed

            '.SetFocus

            '.SelStart = Len(.Value)

            'Let the textbox scroll

            '.SetFocus

            'DoEvents

        End With

    Next

 End Sub

Answer 2

OssieMac, you just won the internet- and if you are ever in Atlanta GA [USA], I owe you a beverage of your choice.

:-)

I've been trained to avoid the  use of 'select' in VBA, and by extension hadn't realized the impact of not setting focus on the control.

Thankyouthankyouthankyou