Combo box using OLEobjects
I have some code(see below), gathered about three years ago from the net, which uses a combo box defined using OLEobjects. (I hope that makes sense - I don't know much about OLE). As best as I can tell everything hangs together and the code works fine, with one slight exception. (In my searches for an answer it appears this code is very popular, but I have not found how to accomplish what I want to.) As can be seen in keydown procedure a test is made for both "tab" and "Enter", whereupon a new cell is selected and the combo box is supposed to disappear. That is true for the "Tab", not surprisingly, as the newly selected cell has no combo box. However, for "Enter", after the new cell is selected, one row down in the same column, and the combo box is displayed in the newly selected cell, the previously selected cell retains a greyed-out version of the combo box. If possible I would like this not to happen. Unfortunately, I cannot find how to do it. any help would be much appreciated. The code now follows:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errHandler
If Target.Count > 1 Then GoTo exitHandler
Set cboTemp = ws.OLEObjects("AddTargets")
On Error Resume Next
If cboTemp.Visible = True Then
With cboTemp
.Top = 10
.Left = 10
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End If
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = ws.Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
Resume exitHandler
End Sub
Private Sub AddTargets_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
'Hide combo box and move to next cell on Tab and Enter
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
Thanks in anticipation.
