VBA Course: Controls (continued)

ScrollBar

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

mode - controls continued

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 :

sb - controls continued

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 :

lists - controls continued

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
lists2 - controls continued

Here is the downloadable Excel file : userform4b.xls

Download VBA course archive (PDF)