Userform With Scrollbar

Using VBA Scrollbar & Slider Control To Select Listbox Items


            If you remember, we used spin buttons in other listbox examples (review New Excel Userform).
With the spin button can be adjusted up or down by only one unit at a time.The VBA scrollbar can be used as more efficient from spin buttons for setting of the adjusting a large range of values  .

In our VBA userform example ,through scrollbar or slider control can be navigated between the data listed in the listbox. We chose to use a horizontal scrollbar and slider for this purpose.

Scrollbar and slider control are dependent on each other. For this purpose, we used the following codes:
Private Sub ScrollBar1_Change()
Dim a As Integer, sat As Long
CommandButton1.Enabled = False
CommandButton2.Enabled = True
CommandButton3.Enabled = True
Slider1 = ScrollBar1
Label4 = ScrollBar1
ListBox1.ListIndex = ScrollBar1 - 1
For a = 0 To 6
Controls("textbox" & a + 1) = Cells(ScrollBar1 + 1, a + 2)
Next
TextBox8 = VBA.Format(Cells(ScrollBar1 + 1, 9), "dd.mm.yyyy")
Range("A2:I" & [a65536].End(3).Row).Interior.ColorIndex = 25
sat = ScrollBar1 + 1
Range("A" & sat & ":I" & sat).Interior.ColorIndex = 6
End Sub
Private Sub Slider1_Change()
ScrollBar1 = Slider1
End Sub

           When the Userform is loaded, the data in the worksheet is listed automatic on the listbox as columns.  By the Userform, a new record can be added to worksheet, changed or deleted.
Automatic id number is assigned for each new record.


         I used a button to increase and decrease the height of userform (to unhide scrollbar & slider). I created a Vba loop and assigned it to the button.At the same time ,this is a nice userform animation.
When creating the Excel userform, I set a height value, when the button is pressed, the userform becomes longer if the height is less than this value, and the userform becomes shorter if it is larger.

excel userform scrollbar slider


excel userform

No comments:

Post a Comment