How to use Scrollbar with UserForm in Excel VBA Easily

Welcome to Excel Avon

Scrollbar with UserForm

DOWNLOAD USED EXCEL FILE FROM HERE>>

In today’s post, we will show you how to use the scroll Button control in Excel VBA, as well as how to use the UserForm options that you have already read. In the previous post, we explained you how to use spin Button control. The Spin Button is a form of control in Excel that comprises two arrows. These arrows allow to increase or decrease values of the linked cell.

Scrollbar control – The scrollbar box can be dragged to change the control’s value in large increments (while keeping the increment small for clicks) which gives the scrollbar the advantage of having a large number of values to choose from and an extremely wide range of values. cover series.

How to use Scrollbar with UserForm

Well, you should know how to insert UserForm, as we have learned in many posts till now, you can insert UserForm. Let us teach you how we insert the UserForm. Let’s understand, then we have to go like last time, first go to the Developer Tab, then click on the option of Visual Basic as shown in the image below.

 

sort-data-on-excel-using-VBA

On opening in VBE, you have to go to Insert and then UserForm has to be inserted, as can be seen in the image.

introduction-of-userform

When we click on the User form, a separate wizard box will be created in a window VBE, in which you can see UserForm1 as the default name. You can increase or decrease the size of the UserForm with the mouse pointer.

Data-entry-with-wizardbox

Now we will insert Scroll Button from toolbox. we can adjust the size of Scroll button according to our own.

scrollbar-button-with-UserForm

Now we’ll run the code.

scrollbar-button-with-UserForm1

When the userform is run, we will get a scroll button which we can also scroll and another button which works when clicked.

scrollbar-button-with-UserForm2

We can also change the color of the scroll bar button, to make it look better, we will change the color of the button, we will go back to VBE.

scrollbar-button-with-UserForm3

After changing the forecolor in the properties, we see the scroll button, then the color has changed. We chose red color, apart from this, you can change the button in many colors.

scrollbar-button-with-UserForm4

The color of the clicking button will be the same and not of the dragging button, we will click on the run button to see this.

scrollbar-button-with-UserForm4.png

Scroll button have a default range from 0 to 32767. but we can change it we will make minimum 0 and maximum 100. 

scrollbar-button-with-UserForm5

We can change the orientation of the scrollbar button, by default it will be Horizontal, but we will make it vertical. You can see in the image below that the orientation of the button is vertical.

scrollbar-button-with-UserForm8

scrollbar-button-with-UserForm67

We will change the orientation of the button back, as the button is by default it will be vertical, we did horizontal to make you understand.

scrollbar-button-with-UserForm13

Here now we will make you an example with a text box so that if you change the value of the scroll bar, you will be able see it in the text box.

scrollbar-button-with-UserForm6

We’ll right click in Scroll button control then we will do coding for Scroll button control.

scrollbar-button-with-UserForm9

Write the code, the value of the textbox will be equal to the value of the spin button.

Private Sub ScrollBar1_Change()
Me.TextBox1.Value = Me.ScrollBar1.Value
End Sub

After writing the code we will go to user form and click on run button.

scrollbar-button-with-UserForm10

On clicking the run button, we will get the form in which if we click on the scroll right button then we will get the increasing numbers in the result, if we use the scroll left button then we will get the decreasing numbers in the result.

scrollbar-button-with-UserForm11

If we drag the drag button of the scroll button towards the right then the integer value will increase and if we drag the drag button towards the left then the integer value will decrease. like we have 100 in max and 0 in minimum. Can get numbers up to 0. Now close Scroll button form.

scrollbar-button-with-UserForm12

Therefore, I hope that you have understood How to use Scrollbar with UserForm in Excel VBA, maybe if you do not understand some options, then you can comment us, which we will answer soon and for more information, you can follow us on Twitter, Instagram, LinkedIn and you can also follow on YouTube.

DOWNLOAD USED EXCEL FILE FROM HERE>>

LEARN MORE TOPIC IN VBA HERE

You can also see well-explained video here about How to use Scrollbar with UserForm in Excel VBA

Leave a Reply