VBA-Урок 12.3. Елементи управління (Controls)

Елементи управління (Controls) також можуть використовуватись поза формами. В наступному прикладі ми використаємо елементи управління прямо на робочому аркуші.

Зазначте, що “Режим дизайнера” (“Design mode”) повинен бути активований, для того, щоби змінювати певний елемент управління розташований на робочому аркуші (і так само має бути деактивований, для того щоб використовувати цей елемент управління).

Смуга прокрутки (ScrollBar)

Перед розглядом наступного прикладу, давайте поглянемо на це:

Зараз, ми хочемо добавити колір заднього фону комірок і вибрати їх базуючись на позиції повзунка у визначеній площі 30 рядків на 10 колонок.

Властивості вертикального повзунка для прокрутки екрану наступні:

Горизонтальний повзунок такий самий, за винятком Max : 10.

Далі йде код, який запускатиметься кожний раз, коли змінюється значення вертикального повзунка (Value):

 'Сірий фон в комірках
 Cells.Interior.Color = RGB(240, 240, 240)

 'Застосовуємо колір та вибираємо комірку
 With Cells(ScrollBar_vertical.Value, ActiveCell.Column) 'Визначаємо комірка, використовуючи Значення (Value)
     .Interior.Color = RGB(255, 220, 100) 'Застосовуємо оранжевий колір
     .Select 'Вибираємо комірку
 End With

Цей код запускається коли виникають події Change та Scroll та виконує інструкції в не залежності від того, по якій частині повзунка ми клацнули.

Далі йде код для вертикального повзунка:

Private Sub vertical_bar()
     'Застосовуємо сірий фон для комірок
     Cells.Interior.Color = RGB(240, 240, 240)
     
     'Застосовуємо задній фон та вибираємо комірку
     With Cells(ScrollBar_vertical.Value, ActiveCell.Column)
         .Interior.Color = RGB(255, 220, 100) 'Оранжевий
         .Select 'Вибираємо комірку
     End With
End Sub

Private Sub ScrollBar_vertical_Change()
     vertical_bar
End Sub

Private Sub ScrollBar_vertical_Scroll()
     vertical_bar
End Sub

А тут, в свою чергу, є код для горизонтального повзунка:

Private Sub horizontal_bar()
     'Застосовуємо сірий фон для комірок
     Cells.Interior.Color = RGB(240, 240, 240)

     'Застосовуємо задній фон та вибираємо комірку
     With Cells(ActiveCell.Row, ScrollBar_horizontal.Value)
         .Interior.Color = RGB(255, 220, 100) 'Оранжевий
         .Select 'Вибираємо комірку
     End With
End Sub

Private Sub ScrollBar_horizontal_Change()
     horizontal_bar
End Sub

Private Sub ScrollBar_horizontal_Scroll()
     horizontal_bar
End Sub

Випадаючий список та Вікно зі списком (ComboBox та ListBox)

Це є початкова точка нашого наступного прикладу:

Ви можете завантажити Excel файл з прикладом: userform4.xls

Під час запуску форми, ми хочемо, щоби 4 країни були завантажені у випадаючий список (використовуючи AddItem метод):

Private Sub UserForm_Initialize()
     For i = 1 To 4 ' => щоби заповнити 4 країни
        ComboBox_Country.AddItem Cells(1, i) 'Добавляємо значення комірок від A1 до A4 використовуючи цикл
    Next
End Sub

Коли значення випадаючого списку змінюєтся, ми хочемо добавити міста вибраної країни, використовуючи цикл, подібний до попереднього.

Для цього нам потрібно знати номер стовпця, а також кількість міст (рядків) у цьому стовпці.

Властивість ListIndex містить порядковий номер вибраного елемента у випадаючому списку (на відміну від Value, яке містить значення елемента списку). Зауважте, що ListIndex починається з номера 0.

Номер колонки отримується через:

column_number = ComboBox_Country.ListIndex + 1

Щоби отримати кількість рядків у вибраній колонці певної країни, ми можемо знайти номер останньої непорожньої комірки:

rows_number = Cells(1, column_number).End(xlDown).Row

Використовуючи цю інформацію, тепер можливо створити цикл, щоби додати міста до списку:

Private Sub ComboBox_Country_Change()
     'Очищаємо список (в іншому випадку, міста будуть додані відразу)
     ListBox_Cities.Clear
     
     Dim column_number As Integer, rows_number As Integer
     
     'Порядковий номер вибраного елемента (ListIndex починається з 0) :
     column_number = ComboBox_Country.ListIndex + 1
     'Кількість рядків у вибраній колонці з країнами :
     rows_number = Cells(1, column_number).End(xlDown).Row

     For i = 2 To rows_number ' => наповнення списку містами
        ListBox_Cities.AddItem Cells(i, column_number)
     Next
End Sub

Примітка: Ми могли б скоротити код, але це б зробило його менш читабельним:

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

Місто, що ми виберемо, буде введене в текстове поле “Вибір” (“Choice”):

Private Sub ListBox_Cities_Click()
     TextBox_Choice.Value = ListBox_Cities.Value
End Sub

Ви можете подивитись готовий приклад в цьому Excel файлі: userform4b.xls