excel-vba
Creazione di un menu a discesa nel foglio di lavoro attivo con una casella combinata

introduzione

Menu di Jimi Hendrix

In generale, il codice viene inserito nel modulo di un foglio.

Questo è l'evento Worksheet_SelectionChange, che si attiva ogni volta che viene selezionata una cella diversa nel foglio attivo. Puoi selezionare "Foglio di lavoro" dal primo menu a discesa sopra la finestra del codice e "Selection_Change" dal menu a discesa accanto ad esso. In questo caso, ogni volta che si attiva una cella, il codice viene reindirizzato al codice della casella combinata.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   ComboBox1_Change
   
End Sub

Qui, la routine dedicata al ComboBox è codificata per l'evento Change per impostazione predefinita. In esso, c'è una matrice fissa, popolata con tutte le opzioni. Non l'opzione CLEAR nell'ultima posizione, che verrà utilizzata per cancellare il contenuto di una cella. L'array viene quindi passato al Combo Box e passato alla routine che esegue il lavoro.

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

L'array viene passato alla routine che riempie le celle con il nome del brano o il valore null per svuotarle. Innanzitutto, a una variabile intera viene assegnato il valore della posizione della scelta effettuata dall'utente. Quindi, la casella combinata viene spostata nell'angolo in alto a sinistra della cella che l'utente attiva e le sue dimensioni regolate per rendere l'esperienza più fluida. Alla cella attiva viene quindi assegnato il valore nella posizione della variabile intera, che tiene traccia della scelta dell'utente. Nel caso in cui l'utente selezioni CANCELLA dalle opzioni, la cella viene svuotata.

L'intera routine si ripete per ogni cella selezionata.

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

Esempio 2: Opzioni non incluse

Questo esempio viene utilizzato per specificare opzioni che potrebbero non essere incluse in un database di alloggi disponibili e relativi servizi.

Si basa sull'esempio precedente, con alcune differenze:

1. Due procedure non sono più necessarie per una singola casella combinata, ottenuta combinando il codice in un'unica procedura.
2. L'uso della proprietà LinkedCell per consentire l'inserimento corretto della selezione utente ogni volta
3. L'inclusione di una funzionalità di backup per garantire che la cella attiva si trovi nella colonna corretta e un codice di prevenzione degli errori, in base all'esperienza precedente, in cui i valori numerici sarebbero stati formattati come stringhe quando inseriti nella cella attiva.

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

La macro precedente viene avviata ogni volta che viene attivata una cella con l'evento SelectionChange nel modulo del foglio di lavoro:

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