excel-vba
Een vervolgkeuzemenu maken in het actieve werkblad met een keuzelijst

Invoering

Jimi Hendrix-menu

Over het algemeen wordt de code in de module van een blad geplaatst.

Dit is de gebeurtenis Worksheet_SelectionChange, die elke keer wordt geactiveerd wanneer een andere cel in het actieve blad wordt geselecteerd. U kunt "Werkblad" selecteren in het eerste vervolgkeuzemenu boven het codevenster en "Selectie wijzigen" in het vervolgkeuzemenu ernaast. In dit geval wordt de code elke keer dat u een cel activeert, omgeleid naar de code van de Combo Box.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   ComboBox1_Change
   
End Sub

Hier wordt de routine die is toegewezen aan de ComboBox standaard gecodeerd naar de gebeurtenis Change. Daarin zit een vaste array, gevuld met alle opties. Niet de optie WISSEN in de laatste positie, die zal worden gebruikt om de inhoud van een cel te wissen. De array wordt vervolgens overgedragen aan de Combo Box en doorgegeven aan de routine die het werk doet.

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

De array wordt doorgegeven aan de routine die de cellen vult met de naam van het nummer of de nulwaarde om ze te legen. Eerst krijgt een integer-variabele de waarde van de positie van de keuze die de gebruiker maakt. Vervolgens wordt de combobox verplaatst naar de linkerbovenhoek van de cel die de gebruiker activeert en worden de afmetingen aangepast om de ervaring vloeiender te maken. De actieve cel krijgt vervolgens de waarde op de positie in de variabele integer toegewezen, die de keuze van de gebruiker volgt. Als de gebruiker WISSEN uit de opties selecteert, wordt de cel geleegd.

De hele routine wordt herhaald voor elke geselecteerde cel.

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

Voorbeeld 2: opties niet inbegrepen

Dit voorbeeld wordt gebruikt bij het opgeven van opties die mogelijk niet zijn opgenomen in een database met beschikbare woningen en de bijbehorende voorzieningen.

Het bouwt voort op het vorige voorbeeld, met enkele verschillen:

1. Twee procedures zijn niet langer nodig voor een enkele keuzelijst, gedaan door de code te combineren in een enkele procedure.
2. Het gebruik van de eigenschap LinkedCell om elke keer de juiste selectie van de gebruikersselectie mogelijk te maken
3. De opname van een back-upfunctie om ervoor te zorgen dat de actieve cel in de juiste kolom staat en een foutpreventiecode, gebaseerd op eerdere ervaringen, waarbij numerieke waarden zouden worden opgemaakt als tekenreeksen wanneer deze naar de actieve cel worden ingevuld.

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

De bovenstaande macro wordt gestart telkens wanneer een cel wordt geactiveerd met de gebeurtenis SelectionChange in de werkbladmodule:

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