Programmatically add/delete a control in a worksheet

Hi,

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.

Re: Programmatically add/delete a control in a worksheet

Well, I didn't know you could do that. Thanks.
But that wasn't exactly the answer I was looking for.
The combobox was just an example. Could be a spinner for fine-tuning or a simple userform, based on the selected cell. Create and remove controls on the fly, using just the code.

Re: Programmatically add/delete a control in a worksheet

Quote from royUK;800220

This example creates a Label which contains unique entries from the column and resizes to the width of the column. I've also added an example for a ListBox.

Thanks! This is the kind of code I was looking for, "dynamic" stuff (just didn't know how to properly phrase it). I'll just need to figure out how it works and implement the technique in my own code.
Very cool and useful stuff. Thank you!

Re: Programmatically add/delete a control in a worksheet

Wow, you are amazing...
I'd been Googling for this kind of stuff for ages without success, and you hacked them up in just minutes. This is so valuable. Thank you so much!