How To Insert Scrolling Text Box In Excel

In this article, I will tell you how to insert a scrolling text box in excel worksheet. It will first insert a TextBox control and a ScrollBar control in the worksheet, and then add VBA macro code to process the scroll bar change event and display text in the TextBox control. It will also tell you how to add a new line break in the TextBox control.

Table of Contents

1. How To Insert Scrolling Text Box In Excel Steps.

1.1 Insert The ScrollBar Control.

First, you need to enable the Developer tab in the Excel ribbon if it is not already visible. You can refer to the article How To Enable Developer Tab And Write VBA Source Code In Excel.
Once you have enabled the Developer tab, click on it and select “Insert” in the Controls group.
There are 2 groups of controls, one is Form Controls, the other is ActiveX Controls, we should use the control objects in the ActiveX Controls group.
In the dropdown menu, select “ScrollBar” under the ActiveX Controls section.
Press your mouse left key to draw the ScrollBar control on the worksheet where you want the scrolling text box to be located.
The size of the scroll bar can be adjusted by clicking and dragging its borders.
If you want to resize or select the scrollbar control, you can click to select the Design Mode button in the Developer tab —> Controls group.
If you do not select the Design Mode button, you can not design the controls on the worksheet includes the scrollbar control and textbox control.
Click to select the Design Mode button, and then right-click on the scroll bar and select “Properties” from the context menu.
In the Properties window that opens, find the “Min” property and change it to 0. This sets the starting point of the scroll bar.
Next, find the “Max” property and set it to the number of lines of text you want to display in the scrolling text box. For example, if you want to display 10 lines of text, set the Max value to 10.
Close the Properties window and right-click on the scroll bar again. This time, select “View Code” from the context menu.
This will open the Visual Basic Editor, where you can enter the code to make the text scroll. In the editor window, you should see some code that looks like this:
```
Private Sub ScrollBar1_Change()
End Sub
```
This is the event procedure for the “Change” event of the scroll bar. We will enter our code between these two lines to make the text scroll.

1.2 Insert The TextBox Control.

To make the text scroll, we need to create a TextBox control that will hold the text.
To do this, you can click on the “TextBox” icon in the Controls group ActiveX Controls section of the Developer tab and draw a text box on the worksheet.

1.3 Add ScrollBar Change Event Process Source Code.

With the TextBox selected, go back to the Visual Basic Editor and modify the code between the Private Sub and End Sub lines as follows:

Private Sub ScrollBar1_Change()
    Dim startLine As Integer
    Dim endLine As Integer
    Dim text As String
        
    startLine = ScrollBar1.Value 'get current value of scroll bar
    endLine = startLine + 9 'display 10 lines of text starting from startLine
    
    'build the text that will be displayed in the TextBox
    For i = startLine To endLine
        text = text & "Text line " & i & vbCrLf
    Next i
    
    TextBox1.Value = text 'set the value of the TextBox to the generated text
End Sub

This code sets up three variables: “startLine“, “endLine“, and “text“.
“startLine” is set to the current value of the scroll bar, which represents the starting line of the text to be displayed.
“endLine” is calculated by adding 9 to “startLine“, since we want to display 10 lines of text at a time.
“text” is an empty string that will hold the text to be displayed in the TextBox control.
The For loop generates the text to be displayed in the TextBox. It loops through the lines of text starting from “startLine” and ending at “endLine“, concatenating each line of text with the string “Text line ” and the line number.
The vbCrLf sequence is used to add a line break between each line of text.
Finally, the value of the TextBox is set to the generated text.
Save the workbook and close the Visual Basic Editor.
Now, when you move the scroll bar, the text in the scrolling text box will change.
Note that you can customize the formatting and content of the text displayed in the TextBox by modifying the code in step 9 to suit your needs.

2. What Does vbCrLf Means?

vbCrLf is a special character sequence in Visual Basic for Applications (VBA) that represents a line break or carriage return.
The vbCrLf constant is composed of two characters: vbCr, which stands for “carriage return”, and vbLf, which stands for “line feed”. Together, these two characters represent the end of a line of text.
In VBA, you can use vbCrLf to insert a line break in a string. For example, the following code will create a string with two lines of text:
```
Dim myString As String
myString = "This is the first line." & vbCrLf & "This is the second line."
```
When this code runs, the variable myString will contain the text “This is the first line.” on the first line, followed by a line break, and then “This is the second line.” on the next line.
When myString is displayed or printed, the line break represented by vbCrLf will cause the text to appear on separate lines.

3. Why vbCrLf Do Not Return To A New Line In Text Box Control?

If you’re experiencing issues where vbCrLf does not seem to be inserting a line break in your TextBox control, there could be a few reasons for this.
Multi-line property: Make sure that the MultiLine property of your TextBox control is set to True. This property allows the TextBox control to accept multiple lines of text and display them vertically.
WordWrap property: If the WordWrap property of your TextBox control is set to False, the text may appear as a single long line instead of wrapping to the next line. Set the WordWrap property to True to allow the text to wrap when it reaches the right edge of the TextBox control.
ScrollBars property: The ScrollBars property of your TextBox control determines whether scroll bars are displayed when the text exceeds the size of the control.
If the ScrollBars property is set to “None“, the text will not automatically wrap to the next line when it reaches the right edge of the control.
Set the ScrollBars property to “Vertical” or “Both” to enable vertical scrolling and allow the text to wrap when it exceeds the size of the control.
Text format: Finally, make sure that you’re using the correct syntax to add a line break with vbCrLf.
In VBA code, you can concatenate the vbCrLf constant with a string using the ampersand (&) operator, like this:
```
myTextBox.Value = "Line 1" & vbCrLf & "Line 2"
```
This code assigns the text “Line 1” to the first line of the TextBox control, followed by a line break, and then “Line 2” on the next line.
If you’re entering text directly into the TextBox control (rather than via VBA code), you can press “Ctrl+Enter” to insert a line break.
If none of these solutions solve the problem, try adjusting the size of the TextBox control or changing its font size to ensure that there is enough space for the text to wrap to the next line.