VBA Course: Controls (continued)
Controls can be used outside of UserForms. In this example, we'll use controls right on a worksheet.
Please note that "Creation Mode" must be activated in order to modify a control that is placed on a worksheet (and likewise deactivated in order to use the control).
In versions d'Excel lower than 2007: the button is on the "Controls Toolbox" toolbar.
Before we go into detail with this example, please have a look at this:
Our goal is to add a background color to a cell and select it based on the position of the scroll bars in the defined 30 row x 10 column area.
The properties of the vertical scroll bar:
- Min: 1
- Max: 30 (because there are 30 rows)
- Value: the position of the bar (in this case, between 1 and 30)
The horizontal bar is exactly the same except that it has a Max of 10 ...
Here is the code that will run each time there is a change to the Value of the vertical scrollbar:
'Gray background color in the cells Cells.Interior.Color = RGB(240, 240, 240) 'Applying color and selecting the cell With Cells(ScrollBar_vertical.Value, ActiveCell.Column) 'Identifying the cell using Value .Interior.Color = RGB(255, 220, 100) 'Applying Orange Color .Select 'Selecting the cell End With
This code will run when the Change and Scroll events are fired and will execute the instructions no matter which part of the scrollbar is clicked.
Here is the code for the vertical scrollbar:
Private Sub vertical_bar() 'Applying gray background color to the cells Cells.Interior.Color = RGB(240, 240, 240) 'Applying background color and selecting the cell With Cells(ScrollBar_vertical.Value, ActiveCell.Column) .Interior.Color = RGB(255, 220, 100) 'Orange .Select 'Selecting the 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 scrollbar:
Private Sub horizontal_bar() 'Applying gray background color to the cells Cells.Interior.Color = RGB(240, 240, 240) 'Applying background color and selecting cell With Cells(ActiveCell.Row, ScrollBar_horizontal.Value) .Interior.Color = RGB(255, 220, 100) 'Orange .Select 'Selecting the cell End With End Sub Private Sub ScrollBar_horizontal_Change() horizontal_bar End Sub Private Sub ScrollBar_horizontal_Scroll() horizontal_bar End Sub
Here is the downloadable Excel file: scrollbar.xls
ComboBox and ListBox
This is the starting point for our next example:
The downloadable Excel file: userform4.xls
When the UserForm is launched, we want the 4 countries to be loaded into the drop-down list (using the AddItem method):
Private Sub UserForm_Initialize() For i = 1 To 4 ' => to list the 4 countries ComboBox_Country.AddItem Cells(1, i) 'Add the values of cells A1 through D1 using the loop Next End Sub
When the value of the drop-down list changes, we want to add the cities in the chosen country using a loop similar to the previous one.
In order to do this, we need the column number and the row number.
The property ListIndex contains the number of the selection in the drop-down list (unlike Value, which contains the value of the list item). Please note that ListIndex begins with the number 0.
So the column number is given by:
column_number = ComboBox_Country.ListIndex + 1
To obtain the number of rows in the chosen country's column, we can search for the row number of the last in a sequence of non-empty cells:
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 area:
Private Sub ComboBox_Country_Change() 'Emptied list area (otherwise the cities are added immediately) ListBox_Cities.Clear Dim column_number As Integer, rows_number As Integer 'The number of the selection (ListIndex starts with 0): column_number = ComboBox_Country.ListIndex + 1 'Number of rows in the chosen country's column: rows_number = Cells(1, column_number).End(xlDown).Row For i = 2 To rows_number ' => to list cities ListBox_Cities.AddItem Cells(i, column_number) Next End Sub
Note: we could have abbreviated the code above, but we haven't because that would make it much 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 that is chosen will then be entered into the text area:
Private Sub ListBox_Cities_Click() TextBox_Choice.Value = ListBox_Cities.Value End Sub
Here is the downloadable Excel file: userform4b.xls