I have a worksheet with certain cells that, when I double-click on them, will show a combobox containing all items already in the column. I used a dynamic range for that. The code is as follows:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim Cel As Range, UniqList As New Collection, i as Long If Target.Row > 2 And Target.Column = 3 Then 'Add items in the dynamic range "MyRange" to UniqList On Error Resume Next For Each Cel In Range("MyRange") UniqList.Add Cel.Value, CStr(Cel.Value) Next Cel On Error GoTo 0 Application.ScreenUpdating = False 'Populate the list to an unused column For i = 1 To UniqList.Count Range("Z" & i).Value = UniqList(i) Next i 'Sort with Excel's Range.Sort method Range("Z1:Z" & UniqList.Count).Sort key1:=Range("Z1") 'Add the sorted list to combobox For Each Cel In Range("Z1:Z" & UniqList.Count) Me.cboCombo.AddItem Cel.Value Next Cel 'Show the combobox within the target cell With Me.cboCombo .Visible = True .Top = Target.Top .Left = Target.Left .Width = Target.Width .Height = Target.Height .LinkedCell = Target.Address .Activate End With End If CleanUp: Range("Z1").EntireColumn.ClearContents Application.ScreenUpdating = True End Sub
And I have another code in the SelectChange event to stash away the combobox when not in use, like this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Stash away the combobox when not in use and clear its contents If Me.cboCombo.Visible Then With Me.cboCombo .Top = 1 .Left = 1 .Height = 10 .Width = 10 .Visible = False .LinkedCell = "" .Clear End With End If End Sub
This works pretty well as long as I use this code in my computer. If I were to import this code into the same worksheet on another computer, I'd have to manually go into the Developer's tab, insert a combobox somewhere in the sheet, name it "cboCombo" and set its initial properties, or else the code won't work.
So my questions are:
1. How do I create this combobox, set its initial properties, and delete after use on-the-fly, using just the VBA code? It's an ActiveX control, not a form control.
2. How do I check if this combobox already exists in the sheet, so I don't have to add it?
The ideal solution would be that, I just import this code, and any worksheet with this layout would get this combobox function, without the need to manually add a new control. And if the control is already there, skip the whoe thing.