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
Display More
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
Display More
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.