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

I am a bit confused when in the post description is "VBA to scroll down a userform textbox (line by line)" and then in the post it appears that you just want to go to the last line.

The following code takes the cursor to the last line. Curline counts from zero and LineCount starts from 1 and hence LineCount - 1.

With Me.TextBox1

        .SetFocus

        .CurLine = .LineCount - 1

End With

If you want it to actually scroll slowly then you could use a loop with a wait incorporated like the following.

    Dim i As Long

    With Me.TextBox1

        .SetFocus

        .CurLine = 0    'Initialize at top

        For i = 0 To .LineCount - 1

            Application.Wait (Now + TimeValue("0:00:01"))

            .CurLine = i

            DoEvents

        Next i

    End With

If you want it to scroll down using the above loop with wait method then place the code in the UserForm_Activate event rather than the Initialize event otherwise it will not display the Userform until after the code is finished running.

Answer 3

OssieMac, I appreciate the reply. You are correct that my ultimate goal is to move the userform textbox so that the last line is showing. The problem is that there appears to be a bug under the conditions in the linked post (wordwrap, vertical scrollbars, multiline, locked) where Excel does not actually scroll to the end (e.g. make the last line(s) visible) even if the cursor is accurately placed at the end of the text by VBA. e.g. I can use a msgbox to tell me the currline, currx, etc and it is all accurate for the end of the text field, the textfield just isn't scrolled to the very bottom in the UI.

The only solution I've found so far is to manually click the scrollbar down to expose the remaining rows, so my original ask (poorly articulated) is that I think I need the VBA code to trigger the scrolldown event, rather than reset the currline location. I can reset Currline and know I'm within 0-3 lines of the end, then use the scrolldown event in a small loop to ensure the last line of text is showing.

In the linked post (re-linking here for convenience) Andreas provides some sample code to make this easy to replicate. It doesn't happen every single time, so you may have to run it a few times to see the effect (and I suggest using narrow textboxes like the ones shown in the screenshots of the linked post, to maximize the effect of the multiline/linewrap which is probably part of what is causing the problem? Just guessing).

I appreciate you taking the time to look at this- it's an obscure problem, but in this particular application, fairly critical for the user to not miss the most recently logged data.

Answer 4

I used the code by Andreas to set up and used my code to position the cursor at the bottom of the textbox and have repeatedly run the code and cannot get it to fail. Have tested again with reduced width of the TextBox and still works fine.

In your testing is the code the last code in the Initialize event or do you have other code running after it? In the past I have known screen updating to get skipped and have surmised that it is due to further code running prior to the screen updating and DoEvents have not fixed but adding line Application.ScreenUpdating = True has fixed.

In the loop code that I provided to scroll down slowly it did sometimes fail to go all the way to the bottom until I added the DoEvents in the loop and after that has not failed.

Have you tried inserting the code in the Userform Activate event instead of Initialize event so that it does not start to run until after the userform is shown. The reason I say this is because with the loop code I provided to slowly scroll down nothing is visible on the screen while it is scrolling down and it makes it quite slow to display the userform and that is the reason that I edited my previous post and suggested to use it in the activate event. This indicates that it is attempting to update the screen with the userform simultaneously with moving the cursor to the bottom and there is possibly a conflict with the screen updating.

Answer 5

My sample code is pasted below the image; I'm using a userform and 2 textboxes with a width of 90 each (Excel 2016 x32 on Win7 x64); I've added a UserForm_Activate, and confirmed that it runs, but am still getting the same effect when the combination of line length and line wrap are just right (or, wrong?). Even if it is only 1 out of 10 runs, that still causes me concern for using in a real-world situation where users might miss important info.

Visual inspection of the scrollbar isn't sufficient- I actually have to manually click the downarrow/scrolldown to reveal the extra lines of text. I'm including an image of my results using the code below; the textbox on the left in this trial run was correctly scrolled to the bottom, the textbox on the right actually had two additional lines of text when manually scrolled

Any additional suggestions for approaches to fix this in code would be greatly appreciated!!  :)

produced from the code:                                                 after manually clicking downarrows on both textboxes:

Private Sub UserForm_Activate()

   Dim Item

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

       Item.SelStart = Len(Item.Value)

       Item.SetFocus

       DoEvents

     Next

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

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

       Next

       .Value = s

       .SelStart = Len(.Value)

       'Let the textbox scroll

       .SetFocus

       DoEvents

     End With

   Next

   Me.TextBox1.SetFocus

 End Sub