Welcome to Excel Avon
Spin Button with UserForm
DOWNLOAD USED EXCEL FILE FROM HERE>>
In today’s post, we will show you how to use the Spin 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 Toggle Button control. Toggle Button is used to show the enabled and unchecked state of a button.
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. Both the buttons are located beside each other, but one works for increasing and the other works for decreasing numeric values.
How to use Spin Button 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.
On opening in VBE, you have to go to Insert and then UserForm has to be inserted, as can be seen in the image.
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.
Now we will insert Spin Button from toolbox. we can adjust the size of spin button according to our own.
Spin button have a default range from 0 to 100. but we can change it we will make minimum 10 and maximum 100.
We can change the orientation of the button, by default it will be vertical, but we will make it horizontal. You can see in the image below that the orientation of the button is horizontal.
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.
By the way, the properties of the spin button are similar to the buttons mentioned in the previous article, so we are paying attention only to some properties. As such, the delay property of a spin button affects the amount of time between successive spin up, spin down, and change events when the user clicks and holds a button on the spin button. The default value of delay is 50 milliseconds. This means that the object fires the first event after 250 milliseconds and each subsequent event after 50 milliseconds.
Now we’ll run the code.
The button has come in the userform, now we will click the button. We can increase or decrease the delay time as per our wish.
Now we will insert a text box from which we will see the result after clicking the button.
We’ll right click in spin button then we will do coding for spin button.
Write the code, the value of the textbox will be equal to the value of the spin button.
Private Sub SpinButton1_Change() Me.TextBox1.Value = Me.SpinButton1.Value End Sub
After writing the code we will go to user form and click on run button.
On clicking the run button, we will get the form in which if we click on the spin up button then we will get the increasing numbers in the result, if we use the spin down button then we will get the decreasing numbers in the result like we have 100 in max and 100 in minimum. Can get numbers up to 10.
Here you can see the minimum and maximum value.
Now close Spin button form.
Therefore, I hope that you have understood How to use Spin Button 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>>
You can also see well-explained video here about How to use Spin Button with UserForm in Excel VBA
