ComboBox does not disappear after selection

[claustro]

Hi all ,
My first message here
I am struggling to make the combo box I am using for selecting employees in my timetable disappear after selection.
It disappears only after clicking on another cell.
Is it possible to make it disapear after the click in the dropdown menu?

Another problem is if I click on the line between 2 cells the cursor makes a jump up or down , so you have to carefully select the cell .

I am a total beginner and I copied the vba code from a tutorial so I am unable to understand how I could fix the code
I tried to use the mini sheet feature but if I try to copy my sheet I receive the error: 1004 run-time referred to Set cboTemp = ws.OLEObjects("TempCombo")

this is the code I am using

'==========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
  Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim Tgt As Range
Set Tgt = Target.Cells(1, 1)
Set ws = ActiveSheet
On Error GoTo errHandler

If Tgt.Validation.Type = 3 Then
    Cancel = True
End If

Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
  With cboTemp
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With
On Error GoTo errHandler

If Tgt.Validation.Type = 3 Then
    Application.EnableEvents = False
    str = Tgt.Validation.Formula1
    str = Right(str, Len(str) - 2)
    With cboTemp
      .Visible = True
      .Left = Tgt.Left
      .Top = Tgt.Top
      .Width = Target.Width + 5
      .Height = Target.Height + 4
      .ListFillRange = ws.Range(str).Address
      .LinkedCell = Tgt.Address
    End With
    cboTemp.Activate
    'open the drop down list automatically
    Me.TempCombo.DropDown
  End If
 
errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub
'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
If cboTemp.Visible = True Then
  With cboTemp
    .Top = 10
    .Left = 10
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End If

errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub
'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
'***NOTE: if KeyDown causes problems, change to KeyUp
'Table with numbers for other keys such as Right Arrow (39)
'https://msdn.microsoft.com/en-us/library/aa243025%28v=vs.60%29.aspx

Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    Select Case KeyCode
        Case 9 'Tab
            ActiveCell.Offset(0, 1).Activate
        Case 13 'Enter
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'do nothing
    End Select
End Sub
'====================================

 

[claustro]

F8 solved the first "problem"

 

[Akuini]

Maybe I solved , I went to proprieties of file and the remove block , now it works.

Glad it works.

I added the extra code you provided for let the drop down menu open as soon as you double click on a cell and it works flawlessy

In your sample file, you put "Private Sub Workbook_SheetBeforeDoubleClick" in the wrong place, it should be in Thisworkbook code module.

1) Show all my 25 items without the need of scrolling it

Try this:
Open vba window
Open VBAProject Search_deList_v2.1.xlam
Open UseForm1
In properties section, select Combobox1
Change Listrow property value to 25

 

[claustro]

Thank you!
I realized that I put Private Sub Workbook_SheetBeforeDoubleClick in the wrong place I uploaded a wrong version
Last request
Is it possible to automatically open the list of items ( i have only 25 records) without pressing the arrow down button?

 

[Akuini]

Last request
Is it possible to automatically open the list of items ( i have only 25 records) without pressing the arrow down button?

Open Userform1 code module.
Replace "Private Sub UserForm_Initialize()" with this one:

Private Sub UserForm_Initialize()

'where the cursor go after leaving the combobox
OffsetRow = 1: OffsetCol = 0

'resizing userform
If xUF_Size(1) <> 0 Then
        Me.Width = xUF_Size(1)
        Me.ComboBox1.Width = xUF_Size(2)
        Me.Label1.Left = xUF_Size(3)
End If
            
'===============================

Call set_Position
Call changeColor


    With Me
        .ComboBox1.MatchEntry = fmMatchEntryNone
        .TextBox1.Text = txb_SearchMode
        .TextBox1.ControlTipText = "Search mode: blank or type 1,2, or 3 in textbox"
        
        If sortFlag = False Then
            .Caption = "Search deList v2.0 - Sort Order: original"
        Else
            .Caption = "Search deList v2.0 - Sort Order: ascending"
        End If
        .ComboBox1.DropDown
    End With

End Sub

 

[claustro]

Perfect!!! thank you !
Do you have a PayPal account so I can offer you a beer?

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

Do you have a PayPal account so I can offer you a beer?

No need, knowing that my work is useful to many people is my reward. But thanks for your kindness.

 

[claustro]

Thank you very much for your time. Just for your curiosity on how far your software has come, it will be used to make the monthly timetable of anesthesia service in the main Orthopedics hospital of Florence Italy

 

[Akuini]

Thank you very much for your time. Just for your curiosity on how far your software has come, it will be used to make the monthly timetable of anesthesia service in the main Orthopedics hospital of Florence Italy

That's good to know.
And feel free to share the add-in with your friends and colleagues if they need it.

 

[claustro]

Excuse me for bothering you again, but is it possible to transfer the saved file with an integrated add-in? There are many people using this file and will be easier for me if I could simply share a file

 

[Akuini]

Excuse me for bothering you again, but is it possible to transfer the saved file with an integrated add-in? There are many people using this file and will be easier for me if I could simply share a file

No, you can't.
If you want you can arrange the macro version of this searchable combobox, so you could simply share the file.
You can find the example here:

Macro to create searchable data validation+combobox

Akuini submitted a new Excel article: Macro to create searchable data validation+combobox - Speed up searching in long list data validation & easy to set various specific behaviors Excel doesn't provide a built-in way to search data validation list. So it could be hard to scroll through the...

www.mrexcel.com

if you need help to set it up, please reply on that thread.