Keep Activex combobox dropdown open after changing the Listfillrange

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.

Re: Keep Activex combobox dropdown open after changing the Listfillrange

forum.ozgrid.com/index.php?attachment/73597/

Here is a sample of what I am trying to do.

Re: Keep Activex combobox dropdown open after changing the Listfillrange

I am trying it with listbox, but when I try to change the Listfillrange with the first selection it fires the change event. The listfillrange is unchanged.

Also, by directing me toward the Listbox, are you saying that what I am trying to do with the combobox is not possible?

Re: Keep Activex combobox dropdown open after changing the Listfillrange

If you look at the code I posted, I got it from the site you linked. Thanks for trying to help.

Re: Keep Activex combobox dropdown open after changing the Listfillrange

What I wanted to do, which Debra's original code does not cover, was to use the same combobox to select from two different named ranges. The problem that I was experiencing, as stated in my original post, is that when I change the listfillrange to the second named range the dropdown will not remain open. I have been trying to change my code using listboxes, per your recommendation. I am running into quirky issues with the listboxes, such as the change event firing twice.

I have added 4 listboxes to the worksheet. 1 which lists the available shifts, and 3 that list the employees on each shift. When the shift listbox loses focus I am clearing the .value. When I make the listbox visible again for the next selection, the first value is highlighted and shows as the value for the listbox. Is there a way to prevent this from happening. The change event only fires if I select one of the other two options. If I selected the top, highlighted option it does not fire the change event since it is already set as the value. I have done much work with macros, but this is my first experience with listboxes. I have been doing lots of research, but have not found a solution yet.