excel-vba
콤보 상자를 사용하여 활성 워크 시트에 드롭 다운 메뉴 만들기

소개

지미 헨드릭스 메뉴

일반적으로 코드는 시트의 모듈에 배치됩니다.

이것은 활성 시트에서 다른 셀을 선택할 때마다 실행되는 Worksheet_SelectionChange 이벤트입니다. 코드 창 위의 첫 번째 드롭 다운 메뉴에서 "워크 시트"를 선택하고 그 옆에있는 드롭 다운 메뉴에서 "Selection_Change"를 선택할 수 있습니다. 이 경우 셀을 활성화 할 때마다 코드가 콤보 상자의 코드로 리디렉션됩니다.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   ComboBox1_Change
   
End Sub

여기서 ComboBox 전용 루틴은 기본적으로 Change 이벤트로 코딩됩니다. 여기에는 모든 옵션이 채워진 고정 배열이 있습니다. 셀의 내용을 지우는 데 사용되는 마지막 위치의 CLEAR 옵션이 아닙니다. 그런 다음 배열은 콤보 상자로 전달되고 작업을 수행하는 루틴으로 전달됩니다.

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

배열은 노래 이름이나 null 값으로 셀을 채우는 루틴으로 전달되어 비어 있습니다. 먼저 정수 변수에 사용자가 선택한 선택 항목의 위치 값이 제공됩니다. 그런 다음 콤보 상자가 사용자가 활성화하는 셀의 위쪽 왼쪽 모서리로 이동하고 크기를 조정하여 경험을 더욱 유동적으로 만듭니다. 그러면 활성 셀에 사용자 변수를 추적하는 정수 변수의 위치에 값이 지정됩니다. 사용자가 옵션에서 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