Hopefully my title is sufficient enough.
My issue is that I am trying to use an activex combobox to populate cells on a sheet which is basically a list of employees and the various stations that they will work at for the shift. I am using data validation in the cells which gives the combobox it's intial Listfillrange. When the user double clicks in the cell, this code runs:
Private Sub Worksheet_BeforeDoubleClick _ (ByVal Target As Range, _ Cancel As Boolean) Dim str As String Dim cboTemp As OLEObject Dim ws As Worksheet Set ws = ActiveSheet Set cboTemp = ws.OLEObjects("TempCombo") On Error Resume Next With cboTemp 'clear and hide the combo box .ListFillRange = "" .LinkedCell = "" .Visible = False End With On Error GoTo errHandler If Target.Validation.Type = 3 Then 'if the cell contains 'a data validation list Cancel = True Application.EnableEvents = False 'get the data validation formula str = Target.Validation.Formula1 str = Right(str, Len(str) - 1) With cboTemp 'show the combobox with the list .Visible = True .Left = Target.Left .Top = Target.Top .Width = Target.Width + 5 .Height = Target.Height + 5 .ListFillRange = str '.LinkedCell = Target.Address End With cboTemp.Activate 'open the drop down list automatically Me.TempCombo.DropDown End If errHandler: Application.EnableEvents = True Exit Sub End Sub
The combobox appears over the activecell with the dropdown open and the user selects the shift which triggers the change event.
Private Sub TempCombo_Change() Dim str As String If Thing = False Then str = TempCombo.Text ActiveCell.Activate If Left(str, 1) = "_" Then With TempCombo .Left = ActiveCell.Left .Top = ActiveCell.Top .Width = ActiveCell.Width + 5 .Height = ActiveCell.Height + 5 .ListFillRange = str .Visible = True .LinkedCell = ActiveCell.Address .Activate .DropDown End With End If End If End Sub
This is where my problem occurs. The combobox dropdown does not stay open for the user to select the employee. It still works, since they can click the dropdown arrow and it will display the list of employees, which they can select and have it populate the cell as intended. In my code, I tell the combobox drop down to open, and it does until the end of the macro and then it closes. I have tried several variations of this, and have been unable to keep the dropdown open. It creates one extra step that should not be needed. Thank you for reading, and I appreciate any insight anyone can give me on this. I have been racking my brain and burning up google looking for answers.