'==========================
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
Dim Expensetype As Range
Dim Position As Range

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
  Position = target.Address
      Call TempCombo_LostFocus(Position)
  End If
  
errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub
'=========================================
Private Sub TempCombo_LostFocus(Position)
MsgBox (Tempcombo)
MsgBox (Position)

   If Tempcombo.Value = "Mileage" Then Range(Position).Offset(0, 5).FormulaArray = "=IF(C4=""Mileage"",INDEX(Mileage_Table,MAX((Mileage_Eff_Date<=Mileage!$F4)*(Company_Match=Mileage!$A4)*(ROW(Mileage_Eff_Date)-7)),3),0)"
  
   Range("M3") = ""
        

  With Me.Tempcombo
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
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 Worksheet_BeforeDoubleClick _
  (ByVal Target As Range, _
    Cancel As Boolean)
    
Dim Position As String

  Position = Target.Address
  
      TempCombo_LostFocus Position
        
errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub
'=========================================
Private Sub TempCombo_LostFocus(ByVal Position As String)

MsgBox (Position)

End Sub