Force Userform Textbox Scrolling to Top

I use my Edit Table Query utility every day to easily modify and test SQL in Excel. The main textbox contains the SQL code, which often fills more than the textbox. The problem is when I click into the textbox it always scrolls to the bottom. Even though this has been happening for months this always catches me off guard. I’m surprised, then annoyed. I finally decided to take action, and came up with some code to force userform textbox scrolling to the top.

The Issue
Here’s an example of what I’m talking about. When I click the New Data button the textbox content looks good, in that the numbers start at one. But as soon as I click into it the content scrolls to the bottom. (To add to my annoyance, the scrollwheel doesn’t work in the textbox.)
textbox scrolling issue

My solutions uses the Textbox’s SelStart and SelLength properties. I set both to 0, meaning that the selection starts before the first character. That’s what the “Force Start at Top” checkbox in the form does. (Download below!)

However, when I added those two lines of code another issue appeared. There was no scrollbar. In fact in the animation above you can see that there’s no scrollbar until I click into the textbox. And below you can see that with the scrolling fix applied there is no scrollbar:

no scrollbar after fix

You can force the scrollbar to appear by arrowing down past the bottom of the visible content. An internet search came up with the solution of setting focus on the textbox. I do this before applying the SelStart/SelLength code. That’s what the “Make scrollbars visible” checkbox does:

textbox scrolling fixed

VBA
Here’s a basic subroutine that takes some text and a button object as parameters. It sets a textbox’s text, sets the focus on the textbox, sets the selection start to zero and sets the focus back to the calling button.

Sub FillTextboxText(TextboxText As String, CallingButton As MSForms.CommandButton)

Me.TextBox1.Text = TextboxText
Me.TextBox1.SetFocus
Me.TextBox1.SelStart = 0
Me.TextBox1.SelLength = 0
CallingButton.SetFocus
End Sub

Other Stuff

Note that the issue with the scrollbar not appearing only occurs once in the life in the userform. In other words, once it has appeared it will always appear. I think.

You might have noticed that the form also has a Same Data button, this button simply saves the textbox contents to a string variable and then set the textbox’s text to that variable. Oddly, when you do this and then click into the textbox no scrolling happens at all, even before the checkboxes are checked. To see this, leave the checkboxes unchecked, click Restart, then click New Data, then scroll halfway up and then click Same Data. There’s no scrolling, even though I’ve done almost the same thing as was done with the New Data button.

This all makes me wonder how MS programmed textbox behavior. It seems almost like it forces the textbox to the bottom to make the scrollbar appear, and that it somehow checks the contents before it changes the scrolling position.

Download

Here’s a workbook with the Userform shown in this post.

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>