excel-vba
Creación de un menú desplegable en la hoja de cálculo activa con un cuadro combinado

Introducción

Jimi Hendrix Menu

En general, el código se coloca en el módulo de una hoja.

Este es el evento Worksheet_SelectionChange, que se activa cada vez que se selecciona una celda diferente en la hoja activa. Puede seleccionar "Hoja de trabajo" en el primer menú desplegable sobre la ventana de código y "Selección_Cambiar" en el menú desplegable que se encuentra al lado. En este caso, cada vez que activa una celda, el código se redirige al código del Combo Box.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   ComboBox1_Change
   
End Sub

Aquí, la rutina dedicada al ComboBox se codifica para el evento Change por defecto. En él, hay una matriz fija, rellenada con todas las opciones. No la opción BORRAR en la última posición, que se utilizará para borrar el contenido de una celda. La matriz se entrega al Combo Box y se pasa a la rutina que hace el trabajo.

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

La matriz se pasa a la rutina que llena las celdas con el nombre de la canción o el valor nulo para vaciarlas. Primero, a una variable entera se le asigna el valor de la posición de la elección que el usuario hace. Luego, el cuadro combinado se mueve a la esquina SUPERIOR IZQUIERDA de la celda que el usuario activa y sus dimensiones se ajustan para que la experiencia sea más fluida. A la celda activa se le asigna el valor en la posición en la variable entera, que sigue la elección del usuario. En caso de que el usuario seleccione BORRAR de las opciones, la celda se vacía.

Toda la rutina se repite para cada celda seleccionada.

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

Ejemplo 2: Opciones no incluidas

Este ejemplo se usa para especificar opciones que podrían no estar incluidas en una base de datos de viviendas disponibles y en las comodidades de los asistentes.

Se basa en el ejemplo anterior, con algunas diferencias:

1. Ya no son necesarios dos procedimientos para un solo cuadro combinado, que se realiza combinando el código en un solo procedimiento.
2. El uso de la propiedad LinkedCell para permitir la entrada correcta de la selección del usuario cada vez
3. La inclusión de una función de copia de seguridad para garantizar que la celda activa se encuentre en la columna correcta y un código de prevención de errores, basado en la experiencia anterior, donde los valores numéricos se formatearían como cadenas cuando se rellenaran con la celda activa.

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 anterior se inicia cada vez que se activa una celda con el evento SelectionChange en el módulo de la hoja de trabajo:

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