excel-vba
コンボボックスでアクティブワークシートのドロップダウンメニューを作成する
サーチ…
前書き
これは、コンボボックスActivexオブジェクトをシートに挿入することにより、ブックのアクティブシートにドロップダウンメニューを作成する方法を示す簡単な例です。 Jimi Hendrixの5曲のうちの1曲を、シートのアクティブなセルに挿入して、それに応じて消去することができます。
ジミヘンドリックスメニュー
一般に、コードはシートのモジュールに配置されます。
これは、アクティブシートで異なるセルが選択されるたびに発生する、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：含まれていないオプション
この例は、利用可能な住宅のデータベースとそれに付随するアメニティに含まれない可能性のあるオプションの指定に使用されます。
前の例を基にしていますが、いくつかの違いがあります。
- 単一のプロシージャにコードを組み合わせることで、単一のコンボボックスで2つのプロシージャが不要になりました。
- LinkedCellプロパティを使用して、毎回ユーザー選択の正しい入力を可能にする
- アクティブなセルを確実にするためのバックアップ機能が含まれており、以前の経験に基づいたエラー防止コードが含まれています。ここでは、アクティブなセルに入力されると数値が文字列としてフォーマットされます。
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
