Combo Box Code
 

I have created a combo box, and validated certain cells with a drop down
list. I want to be able to double click the validated cells to access the
combo box. I set up the combo box, and defined a dynamic range, then
validated the cells I want with a list. The drop down windo works, but it's
not the combo box with the auto complete, font, and size options I chose. I
then used this cobe in the VB editor window to tell excel to hide the combo
box and show it when I double click a validated cell.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, _
Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
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
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 = ws.Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
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

When I go back to my worksheet and try to double click one of my validated
cells I get an error message that says:

method 'OLEObjects'-worksheet' failed


I'm not sure how to debug this, any suggestions would help....