Controls can also be used outside of forms. In the following example, we will use the controls directly on the worksheet.
Note that "Design mode" must be activated in order to change the control located on the worksheet (and must also be deactivated in order to use this control).
Before ironing out this example, let's take a look at this:
Now, we want to add the background color of the cells and select it based on the position of the slider in the defined area of 30 rows by 10 columns.
Vertical slider properties for scrolling the screen.
The horizontal slider is the same, except Max : 10.
The following is the code that will run every time the (Value) value of the vertical slider changes:
'Gray background in cells
Cells.Interior.Color = RGB(240, 240, 240)
We apply color and select a cell
With Cells(ScrollBar_vertical.Value, ActiveCell.Column) 'We define a cell using Value
.Interior.Color = RGB(255, 220, 100) 'Apply orange color
.Select 'Select a cell
End With
This code runs when the Change and Scroll events occur and will run instructions regardless of which part of the slider we clicked on.
Next is the code for the vertical slider:
Private Sub vertical_bar()
We apply a gray background for the cells
Cells.Interior.Color = RGB(240, 240, 240)
Apply the background and select the cell
With Cells(ScrollBar_vertical.Value, ActiveCell.Column)
.Interior.Color = RGB(255, 220, 100) 'Orange
.Select 'Select a cell
End With
End Sub
Private Sub ScrollBar_vertical_Change()
vertical_bar
End Sub
Private Sub ScrollBar_vertical_Scroll()
vertical_bar
End Sub
And here is the code for the horizontal slider:
Private Sub horizontal_bar()
We apply a gray background for the cells
Cells.Interior.Color = RGB(240, 240, 240)
Apply the background and select the cell
With Cells(ActiveCell.Row, ScrollBar_horizontal.Value)
.Interior.Color = RGB(255, 220, 100) 'Orange
.Select 'Select a cell
End With
End Sub
Private Sub ScrollBar_horizontal_Change()
horizontal_bar
End Sub
Private Sub ScrollBar_horizontal_Scroll()
horizontal_bar
End Sub
You can download an Excel file with an example: scrollbar.xls
This is the starting point for our next example:
You can download an Excel file with an example: userform4.xls
When the form is launched, we want the 4 countries to be loaded into the dropdown list (using the AddItem method):
Private Sub UserForm_Initialize()
For i = 1 To 4 ' => to fill 4 countries
ComboBox_Country.AddItem Cells(1, i) 'Add the values of cells from A1 to A4 using a loop
Next
End Sub
When the value of the dropdown list changes, we want to add the cities of the selected country using a loop similar to the previous one.
In order to do this, we need the column number and the row number.
The ListIndex property contains the serial number of the selected item in the drop-down list (as opposed to Value, which contains the value of the list item). Note that the ListIndex starts with the number 0.
The column number is obtained through:
column_number = ComboBox_Country.ListIndex + 1
To get the number of rows in the selected column for a specific country, we can find the number of the last non-empty cell:
rows_number = Cells(1, column_number).End(xlDown).Row
Using this information, it is now possible to create a loop to add the cities to the list:
Private Sub ComboBox_Country_Change()
Clearing the list (otherwise, cities will be added immediately)
ListBox_Cities.Clear
Dim column_number As Integer, rows_number As Integer
'Serial number of the selected element (ListIndex starts with 0) :
column_number = ComboBox_Country.ListIndex + 1
Number of rows in the selected column with countries:
rows_number = Cells(1, column_number).End(xlDown).Row
For i = 2 To rows_number ' => filling the list with cities
ListBox_Cities.AddItem Cells(i, column_number)
Next
End Sub
Note: We could shorten the code, but that would make it less readable:
Private Sub ComboBox_Country_Change()
ListBox_Cities.Clear
For i = 2 To Cells(1, ComboBox_Country.ListIndex + 1).End(xlDown).Row
ListBox_Cities.AddItem Cells(i, ComboBox_Country.ListIndex + 1)
Next
End Sub
The city we choose will be entered in the text field:
Private Sub ListBox_Cities_Click()
TextBox_Choice.Value = ListBox_Cities.Value
End Sub
Here you can see an example in a finished Excel file: userform4b.xls