excel-vba
Создание выпадающего меню в Active Worksheet с помощью Combo Box

Вступление

Меню пользователя Jimi Hendrix

В общем, код помещается в модуль листа.

Это событие Worksheet_SelectionChange, которое срабатывает каждый раз, когда на активном листе выбрана другая ячейка. Вы можете выбрать «Рабочий лист» из первого раскрывающегося меню над окном кода и «Selection_Change» в раскрывающемся меню рядом с ним. В этом случае каждый раз, когда вы активируете ячейку, код перенаправляется на код Combo Box.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   ComboBox1_Change
   
End Sub

Здесь подпрограмма, посвященная ComboBox, по умолчанию кодируется для события Change. В нем есть фиксированный массив, заполненный всеми параметрами. Не опция CLEAR в последней позиции, которая будет использоваться для очистки содержимого ячейки. Затем массив передается в Combo Box и переходит к рутине, которая выполняет эту работу.

Private Sub ComboBox1_Change()

Dim myarray(0 To 5)
    myarray(0) = "Hey Joe"
    myarray(1) = "Little Wing"
    myarray(2) = "Voodoo Child"
    myarray(3) = "Purple Haze"
    myarray(4) = "The Wind Cries Mary"
    myarray(5) = "CLEAR"
    
    With ComboBox1
        .List = myarray()
    End With

    FillACell myarray()

End Sub

Массив передается в подпрограмму, которая заполняет ячейки именем песни или нулевым значением для их удаления. Во-первых, целочисленной переменной присваивается значение позиции выбора, которую делает пользователь. Затем Combo Box перемещается в верхний левый угол ячейки, который пользователь активирует, и его размеры настраиваются так, чтобы сделать его более жидким. Затем активной ячейке присваивается значение в позиции в целочисленной переменной, которая отслеживает выбор пользователя. Если пользователь выбирает CLEAR из опций, ячейка освобождается.

Вся процедура повторяется для каждой выбранной ячейки.

Sub FillACell(MyArray As Variant)

Dim n As Integer

n = ComboBox1.ListIndex

ComboBox1.Left = ActiveCell.Left
ComboBox1.Top = ActiveCell.Top
Columns(ActiveCell.Column).ColumnWidth = ComboBox1.Width * 0.18

ActiveCell = MyArray(n)

If ComboBox1 = "CLEAR" Then
    Range(ActiveCell.Address) = ""
End If

End Sub

Пример 2: варианты не включены

Этот пример используется при указании параметров, которые не могут быть включены в базу данных доступного жилья и сопутствующих им услуг.

Он основывается на предыдущем примере с некоторыми отличиями:

1. Две процедуры больше не нужны для одного комбинированного блока, который выполняется путем объединения кода в одну процедуру.
2. Использование свойства LinkedCell позволяет каждый раз правильно вводить пользовательский выбор
3. Включение функции резервного копирования для обеспечения активной ячейки находится в правильном столбце и кодеке предотвращения ошибок на основе предыдущего опыта, где числовые значения будут отформатированы как строки при заполнении активной ячейки.

Private Sub cboNotIncl_Change()

Dim n As Long
Dim notincl_array(1 To 9) As String

n = myTarget.Row
            
    If n >= 3 And n < 10000 Then
             
        If myTarget.Address = "$G$" & n Then
                
            'set up the array elements for the not included services
            notincl_array(1) = "Central Air"
            notincl_array(2) = "Hot Water"
            notincl_array(3) = "Heater Rental"
            notincl_array(4) = "Utilities"
            notincl_array(5) = "Parking"
            notincl_array(6) = "Internet"
            notincl_array(7) = "Hydro"
            notincl_array(8) = "Hydro/Hot Water/Heater Rental"
            notincl_array(9) = "Hydro and Utilities"
            
            cboNotIncl.List = notincl_array()
                    
        Else
                
            Exit Sub
                
        End If

        With cboNotIncl
                    
            'make sure the combo box moves to the target cell
            .Left = myTarget.Left
            .Top = myTarget.Top
                    
            'adjust the size of the cell to fit the combo box
            myTarget.ColumnWidth = .Width * 0.18
                    
            'make it look nice by editing some of the font attributes
            .Font.Size = 11
            .Font.Bold = False
                        
            'populate the cell with the user choice, with a backup guarantee that it's in column G
                
            If myTarget.Address = "$G$" & n Then
                    
                    .LinkedCell = myTarget.Address
                    
                    'prevent an error where a numerical value is formatted as text
                    myTarget.EntireColumn.TextToColumns
                    
            End If
                
        End With
                
    End If 'ensure that the active cell is only between rows 3 and 1000
            
End Sub

Вышеуказанный макрос запускается каждый раз, когда ячейка активируется событием SelectionChange в модуле рабочего листа:

Public myTarget As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Set myTarget = Target

    'switch for Not Included
    If Target.Column = 7 And Target.Cells.Count = 1 Then

        Application.Run "Module1.cboNotIncl_Change"

    End If

End Sub