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

配列は、セルを空にするためにソング名またはヌル値でセルを塗りつぶすルーチンに渡されます。まず、整数変数には、ユーザーが選択した選択肢の位置の値が与えられます。次に、コンボボックスは、ユーザーがアクティブにしたセルの上端の隅に移動され、その寸法が調節されて、より多くの体感が得られます。アクティブなセルには、ユーザーの選択を追跡する整数変数の位置に値が割り当てられます。ユーザーがオプションから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つのプロシージャが不要になりました。
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