View Full Version : [SOLVED] Combo box using OLEobjects

11-07-2013, 02:44 PM
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


End If

Application.EnableEvents = True

Exit Sub
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.

11-07-2013, 06:19 PM
I have found the answer. It happens if the sheet is protected, although the exact mechanics of this are beyond my simple brain. Sorry to be such a dummy.