excel-vba
Tworzenie menu rozwijanego w aktywnym arkuszu za pomocą pola kombi

Wprowadzenie

Menu Jimi Hendrix

Zasadniczo kod jest umieszczony w module arkusza.

Jest to zdarzenie Worksheet_SelectionChange, które jest uruchamiane za każdym razem, gdy w aktywnym arkuszu wybierana jest inna komórka. Możesz wybrać „Arkusz roboczy” z pierwszego menu rozwijanego powyżej okna kodu oraz „Selection_Change” z rozwijanego menu obok niego. W takim przypadku za każdym razem, gdy aktywujesz komórkę, kod jest przekierowywany do kodu Combo Box.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   ComboBox1_Change
   
End Sub

Tutaj procedura dedykowana dla ComboBox jest domyślnie zakodowana w zdarzeniu Change. W nim jest stała tablica, wypełniona wszystkimi opcjami. Nie opcja WYCZYŚĆ w ostatniej pozycji, która zostanie użyta do wyczyszczenia zawartości komórki. Tablica jest następnie przekazywana do pola kombi i przekazywana do procedury, która wykonuje pracę.

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

Tablica jest przekazywana do procedury wypełniającej komórki nazwą utworu lub wartością zerową, aby je opróżnić. Po pierwsze, zmienna całkowita jest podawana wartości pozycji wybranej przez użytkownika. Następnie pole kombi jest przenoszone do górnego lewego rogu komórki aktywowanej przez użytkownika, a jego wymiary dostosowywane w celu zwiększenia płynności. Aktywnej komórce przypisuje się następnie wartość w pozycji w zmiennej całkowitej, która śledzi wybór użytkownika. W przypadku wybrania przez użytkownika opcji CLEAR z opcji komórka zostaje opróżniona.

Cała procedura powtarza się dla każdej wybranej komórki.

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

Przykład 2: Opcje nieuwzględnione

Ten przykład służy do określania opcji, które mogą nie zostać uwzględnione w bazie danych dostępnych mieszkań i związanych z nimi udogodnień.

Opiera się na poprzednim przykładzie, z pewnymi różnicami:

1. Dwie procedury nie są już konieczne dla pojedynczego pola kombi, wykonując przez połączenie kodu w jedną procedurę.
2. Zastosowanie właściwości LinkedCell w celu umożliwienia prawidłowego wprowadzania wyboru użytkownika za każdym razem
3. Włączono funkcję tworzenia kopii zapasowej, aby upewnić się, że aktywna komórka znajduje się w poprawnej kolumnie, oraz kod zapobiegający błędom, oparty na wcześniejszych doświadczeniach, w którym wartości liczbowe byłyby sformatowane jako ciągi po zapełnieniu do aktywnej komórki.

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

Powyższe makro jest inicjowane za każdym razem, gdy komórka jest aktywowana zdarzeniem SelectionChange w module arkusza:

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