In a vertical ScrollBar clicking up the scroll arrow decreases the value whereas clicking up the scroll arrow on a vertical Spinner increases the value. A SpinButton control does not have a LargeChange property, like in a ScrollBar. SmallChange Property specifies the incremental change, as an integer value (Long variable), that occurs when a user clicks the scroll arrow. A SpinButton control (also referred to as a Spinner control) functions like a ScrollBar control, with similar properties (viz. TextBox, Label, …) or the value in a worksheet range. a number, date, time, etc.) displayed by other UserForm controls (viz. Label4.Caption = "Monthly Instalment: $" & Round(mi, 2) * -1Ī SpinButton control, similar to a ScrollBar control, is used to increment or decrement the value (viz. 'Label displays the monthly instalment, rounded off to 2 decimal points: Mi = Pmt((TextBox2.Value / 100) / 12, TextBox3.Value * 12, TextBox1.Value) MsgBox "Please Enter Annual Interest Rate!" 'calculates the Monthly Instalment using the excel PMT function: 'in re of Loan Tenure, clicking the scroll arrow will increment year by 0.5 and clicking between a scroll arrow and the scroll box will increment year by 2: 'in re of Annual Interest Rate, clicking the scroll arrow will increment rate by 0.1% and clicking between a scroll arrow and the scroll box will increment rate by 1%: 'in re of Loan Amount, clicking the scroll arrow will increment amount by $5,000 and clicking between a scroll arrow and the scroll box will increment amount by $100,000: 'set properties for Label which displays Monthly Instalment: ScrollBar3.Orientation = fmOrientationHorizontal
'set properties for controls in re of Loan Tenure: ScrollBar2.Orientation = fmOrientationHorizontal 'set properties for controls in re of Annual Interest Rate: ScrollBar1.Orientation = fmOrientationHorizontal 'set properties for controls in re of Loan Amount: 'Set properties of Controls on initialization of UserForm. Where width is more than height, ScrollBar is Horizontal and where height is more than width, ScrollBar is Vertical (ii) FmOrientationVertical (Value 0) - vertical ScrollBar and (iii) FmOrientationHorizontal (Value 1) - horizontal ScrollBar.Įxample 1: Creating a Mortgage Calculator, using ScrollBar controls. It has 3 settings: (i) fmOrientationAuto (Value -1) - this is the default value wherein the ScrollBar dimensions automatically determine whether the ScrollBar is Vertical or Horizontal. Orientation Property determines a vertical ScrollBar or a horizontal ScrollBar. In a horizontal ScrollBar clicking the right scroll arrow increases the value and the rightmost position displays the maximum value (will be reverse when you click the left scroll arrow). In a vertical ScrollBar clicking down the scroll arrow increases the value and the lowest position displays the maximum value (will be reverse when you click up the scroll arrow). Min and Max Properties are integer values (Long variable) which specify the minimum and maximum acceptable values of the ScrollBar control (for the Value property setting). The default value is 1 for both the properties. The LargeChange Property specifies the incremental change when the user clicks between a scroll arrow and the scroll box.
ScrollBar & SpinButton controls - the difference between the two is that the ScrollBar box can be dragged to change the control's value over larger increments (while keeping the increment low for clicks) which advantages a ScrollBar to make a selection from across a large number of values and cover an extremely wide range.
It scrolls through a range of values when a user: (i) clicks on the scroll arrows (ii) drags the scroll box or (iii) clicks in an area between a scroll arrow and the scroll box.
#Spin button excel openoffice code
Note: In below given examples, vba codes are required to be entered in the Code Module of the UserForm, unless specified otherwise.Ī ScrollBar control enables to change (increment or decrement) the value displayed by other UserForm controls (viz.
UserForm and Controls - Properties " for properties common to the UserForm and most Controls Using ActiveX Controls on a Worksheet have been illustrated in detail, in the separate section of " Excel VBA: ActiveX Controls, Form Controls & AutoShapes on a Worksheet".Īlso refer " 2. By itself, a UserForm will not be of much use unless ActiveX controls are added to it which are the actual user-interactive objects. UserForm acts as a container in which you add multiple ActiveX controls, each of which has a specific use and associated properties. UserForm Controls - ScrollBar and SpinButton