excel-vba Creating a drop-down menu in the Active Worksheet with a Combo Box Jimi Hendrix Menu
Example
In general, the code is placed in the module of a sheet.
This is the Worksheet_SelectionChange event, which fires each time a different cell is selected in the active sheet. You can select "Worksheet" from the first drop-down menu above the code window, and "Selection_Change" from the drop down menu next to it. In this case, every time you activate a cell, the code is redirected to the Combo Box's code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ComboBox1_Change
End Sub
Here, the routine dedicated to the ComboBox is coded to the Change event by default. In it, there is a fixed array, populated with all the options. Not the CLEAR option in the last position, which will be used to clear the contents of a cell. The array then is handed to to the Combo Box and passed to the routine that does the work.
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
The array is passed to the routine that fills the cells with the song name or null value to empty them. First, an integer variable is given the value of the position of the choice that the user makes. Then, the Combo Box is moved to the TOP LEFT corner of the cell the user activates and its dimensions adjusted to make the experience more fluid. The active cell is then assigned the value in the position in the integer variable, which tracks the user choice. In case the user selects CLEAR from the options, the cell is emptied.
The entire routine repeats for each selected cell.
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
