Floating Worksheet Combo Boxes
It is possible to have a worksheet combo box "float" to the active cell using the code sample below. The developer would create a single combo box on top of a worksheet (not a UserForm) and assign it a name.
Several procedures must be considered:
(1) The Linked cell which receives the result of the combo box needs to be specified in the VBA
(2) The location and size of the combo box needs to be adjusted to the ActiveCell
(3) The Combo Box needs to be initialized when the Workbook is opened
The way the example below works is that the user will click a worksheet cell. if the ActiveCell clicked is within the range specified for the ComboBox (The Application.Intersect range), then the combo box is moved on top of the active cell. When the user selects a drop-down value, that value is transferred to the contents of the active cell. This is one method where a form is not required to use an Active-X ComboBox. The ComboBox is actually created on top of a worksheet.
Program Code
' ***********************************************************
' In Workbook Events Section Load The Combo Box Contents
' ***********************************************************
Option Explicit
Private Sub Workbook_Open()
With Sheets(1).cboCustomer
.Clear
.AddItem "Alberta"
.AddItem "Bakersfield"
.AddItem "Chicago"
.AddItem "Detroit"
.AddItem "Eugene"
.AddItem "France"
.AddItem "Georgia"
.AddItem "Hawaii"
.AddItem "Idaho"
End With
End Sub
' ***********************************************************
' In Worksheet Events Section Detect A Change In The
' Allowed Range And Move The Box To That Location
' ***********************************************************
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Me.cboCustomer
If Not Intersect(Range("A2:A31"), ActiveCell) Is Nothing Then
.Top = ActiveCell.Top
.Left = ActiveCell.Left
.Width = ActiveCell.Width
.LinkedCell = ActiveCell.Address
.Visible = True
Else
.Visible = False
.LinkedCell = ""
End If
End With
End Sub