How to Make a Drop-Down Arrow Visible at All Times in Excel

Poupulate a combo box control in Excel.

Making a drop-down arrow control visible at all times in Microsoft Excel can be accomplished by following a few simple steps. A drop-down control in Excel is also known as a “ComboBox” control. A ComboBox control is a combination of a text box and a list box. The control displays an arrow showing a list of items that you can choose from. Use the “Worksheet_SelectionChange” event to change the location of the drop-down control every time you click a new cell in Excel.

Instructions

1. Launch Microsoft Office Excel, click the “Developer” tab and select the “Insert” icon on the Controls pane. Click the “Combo Box” control and click any cell on the worksheet to add a new drop-down arrow control.

2. Click “Visual Basic” to launch the VB Editor and double-click “Sheet1” to open the VBA module. Add the following code to make the drop-down control visible at all times in your worksheet:

   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   With ActiveWindow.VisibleRange
   ActiveSheet.Shapes("Drop Down 1").Top = .Top + 5
   ActiveSheet.Shapes("Drop Down 1").Left = .Left + .Width - ActiveSheet.Shapes("Drop Down 1").Width - 45
   End With
   End Sub

3. Switch back to Microsoft Excel and scroll down to row 100. Click any cell to make your drop-down control visible.

References

• MrExcel.com: Make A Control Visible at All Times in Excel