Excel VBA

Excel UserForm Controls - ScrollBar and SpinButton; create Mortgage Calculator using ScrollBar

User Rating:  / 5
PoorBest 
Details

 

UserForm Controls - ScrollBar and SpinButton

 

Also refer "2. UserForm and Controls - Properties" for properties common to the UserForm and most Controls

Note: In below given examples, vba codes are required to be entered in the Code Module of the UserForm, unless specified otherwise.


___________________________________________________________________________


Excel VBA UserForms and Controls

 

1. UserForms in Excel VBA - UserForm Basics; Add Controls dynamically at run-time using the Add Method; UserForm Controls (A Snapshot).

2. UserForm and Controls - Properties.

3. UserForm Controls - Label, TextBox and CommandButton.

4. UserForm Controls - ComboBox and ListBox.

5. UserForm Controls - CheckBox, OptionButton and ToggleButton.

6. UserForm Controls - Frame, MultiPage and TabStrip.

7. UserForm Controls - ScrollBar and SpinButton

8. UserForm Controls - Image and RefEdit.

___________________________________________________________________________ 


 

ScrollBar Control

 

A ScrollBar control enables to change (increment or decrement) the value displayed by other UserForm controls (viz. TextBox, Label, …) or the value in a worksheet 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. 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.

 

SmallChange Property specifies the incremental change, as an integer value (Long variable), that occurs when a user clicks the scroll arrow. The LargeChange Property specifies the incremental change when the user clicks between a scroll arrow and the scroll box. The default value is 1 for both the properties.

 

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). 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). 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).

 

Orientation Property determines a vertical ScrollBar or a horizontal ScrollBar. 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. 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.

 

 

 

Example 1: Creating a Mortgage Calculator, using ScrollBar controls. Refer Image 29. See vba codes below: 

 

 

Private Sub UserForm_Initialize()
'Set properties of Controls on initialization of UserForm.


'set properties for controls in re of Loan Amount:
TextBox1.BackColor = RGB(255, 255, 0)
TextBox1.TextAlign = fmTextAlignCenter
TextBox1.Font.Bold = True
TextBox1.Enabled = False


Label1.Caption = "Loan Amount ($):"
Label1.TextAlign = fmTextAlignLeft


ScrollBar1.Min = 0
ScrollBar1.Max = 10000
ScrollBar1.Orientation = fmOrientationHorizontal
ScrollBar1.SmallChange = 5
ScrollBar1.LargeChange = 100
ScrollBar1.Value = 0


'set properties for controls in re of Annual Interest Rate:
TextBox2.BackColor = RGB(255, 255, 0)
TextBox2.TextAlign = fmTextAlignCenter
TextBox2.Font.Bold = True
TextBox2.Enabled = False


Label2.Caption = "Annual Int Rate (%):"
Label2.TextAlign = fmTextAlignLeft


ScrollBar2.Min = 0
ScrollBar2.Max = 1000
ScrollBar2.Orientation = fmOrientationHorizontal
ScrollBar2.SmallChange = 1
ScrollBar2.LargeChange = 10
ScrollBar2.Value = 0


'set properties for controls in re of Loan Tenure:
TextBox3.BackColor = RGB(255, 255, 0)
TextBox3.TextAlign = fmTextAlignCenter
TextBox3.Font.Bold = True
TextBox3.Enabled = False


Label3.Caption = "Loan Tenure (Yrs)"
Label3.TextAlign = fmTextAlignLeft


ScrollBar3.Min = 0
ScrollBar3.Max = 50
ScrollBar3.Orientation = fmOrientationHorizontal
ScrollBar3.SmallChange = 1
ScrollBar3.LargeChange = 4
ScrollBar3.Value = 0


'set properties for Label which displays Monthly Instalment:
Label4.Caption = "Monthly Instalment: $"
Label4.TextAlign = fmTextAlignCenter
Label4.BackColor = RGB(0, 255, 0)
Label4.Font.Bold = True


End Sub

 

 

 

Private Sub ScrollBar1_Change()


'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:
TextBox1.Value = ScrollBar1.Value * 1000
TextBox1.Value = "$" & Format(TextBox1.Value, "#,##0")


End Sub

 

 

 

Private Sub ScrollBar2_Change()


'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%:
TextBox2.Value = ScrollBar2.Value / 10


End Sub

 

 

 

Private Sub ScrollBar3_Change()


'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:
TextBox3.Value = ScrollBar3.Value / 2


End Sub

 

 

 

Private Sub CommandButton1_Click()
'calculates the Monthly Instalment using the excel PMT function:


Dim mi As Currency


If Not TextBox1.Value > 0 Then
MsgBox "Please Enter Loan Amount!"
Exit Sub
ElseIf Not TextBox2.Value > 0 Then
MsgBox "Please Enter Annual Interest Rate!"
Exit Sub
ElseIf Not TextBox3.Value > 0 Then
MsgBox "Please Enter Loan Tenure!"
Exit Sub
Else
mi = Pmt((TextBox2.Value / 100) / 12, TextBox3.Value * 12, TextBox1.Value)


'Label displays the monthly instalment, rounded off to 2 decimal points:
Label4.Caption = "Monthly Instalment: $" & Round(mi, 2) * -1
End If


End Sub

 

 

 

Private Sub CommandButton2_Click()
'close button unloads the UserForm


Unload Me


End Sub

 

 

----------------------------------------------------------------------------------------------------------------------------

 

SpinButton Control

 

A SpinButton control, similar to a ScrollBar control, is used to increment or decrement the value (viz. a number, date, time, etc.) displayed by other UserForm controls (viz. TextBox, Label, …) or the value in a worksheet range. A SpinButton control (also referred to as a Spinner control) functions like a ScrollBar control, with similar properties (viz. SmallChange, Min, Max, Orientation, ...). SmallChange Property specifies the incremental change, as an integer value (Long variable), that occurs when a user clicks the scroll arrow. A SpinButton control does not have a LargeChange property, like in a ScrollBar. 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.

 

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.

 

 

Example 2: Using a SpinButton control to change dates in TextBox, within a specified range:

 

Private Sub UserForm_Initialize()
'populate a date in the TextBox


Dim dt As Date


'disallow manual input in TextBox
TextBox1.Enabled = False


dt = "09/15/2011"
TextBox1.Text = dt


End Sub

 

 

Private Sub SpinButton1_SpinUp()
'increase date by one day at a time, within the same month:


Dim dtUpper As Date


dtUpper = "09/30/2011"


If DateValue(TextBox1.Text) < dtUpper Then
TextBox1.Text = DateValue(TextBox1.Text) + 1
End If


End Sub

 

 

Private Sub SpinButton1_SpinDown()
'decrease date by one day at a time, within the same month:


Dim dtLower As Date


dtLower = "09/01/2011"

If DateValue(TextBox1.Text) > dtLower Then
TextBox1.Text = DateValue(TextBox1.Text) - 1
End If


End Sub

 

 

 

 

Example 3: Move ListBox Items Up/Down in the list order and in the worksheet range, using the SpinButton control (by clicking Up or Down the scroll arrow). Refer Image 30. See below vba codes:

 

 

 Private Sub loadListBox()
'load ListBox from worksheet range:


Dim n As Integer
Dim cell As Range
Dim rng As Range

    
Set rng = Sheet7.Range("A1:A6")


For n = 1 To ListBox1.ListCount
ListBox1.RemoveItem ListBox1.ListCount - 1
Next n


For Each cell In rng.Cells
Me.ListBox1.AddItem cell.Value
Next cell


End Sub

 

 

Private Sub UserForm_Initialize()
'loads ListBox on initializing of UserForm


loadListBox


End Sub

 

 

Private Sub SpinButton1_SpinUp()
'clicking up the scroll arrow moves the selected ListBox item one up both in the list order and also in the linked worksheet range:


Dim n As Long


n = ListBox1.ListIndex


If n > 0 Then


Sheet7.Range("A" & n + 1).Value = Sheet7.Range("A" & n).Value
Sheet7.Range("A" & n).Value = ListBox1.Value


loadListBox


ListBox1.Selected(n - 1) = True


ElseIf ListBox1.ListIndex = 0 Then
MsgBox "First Item cannot be moved Up!"


Else
MsgBox "Please select item!"

 

End If

 

End Sub

 

 

Private Sub SpinButton1_SpinDown()
'clicking down the scroll arrow moves the selected ListBox item one down both in the list order and also in the linked worksheet range:


Dim n As Long


n = ListBox1.ListIndex


If n >= 0 And n < ListBox1.ListCount - 1 Then


Sheet7.Range("A" & n + 1).Value = Sheet7.Range("A" & n + 2).Value
Sheet7.Range("A" & n + 2).Value = ListBox1.Value


loadListBox


ListBox1.Selected(n + 1) = True


ElseIf ListBox1.ListIndex = ListBox1.ListCount - 1 Then
MsgBox "Last Item cannot be moved Down!"


Else
MsgBox "Please select item!"


End If


End Sub

 

 

Private Sub CommandButton1_Click()
'clicking the Close button unloads the UserForm


Unload Me


End Sub

 

 

 

   

Bottom Ad

   
   
© 2014 GlobaliConnect.com. All rights reserved.