Drop down list with search-as-you-type behaviour
 

Hello,

We have to provide a drop down list in a worksheet and have implemented the
feature as described in http://www.contextures.com/xlDataVal11.html. Works ok
so far and fulfils the requirement of searching a matching entry as you type.

As we have very demanding people in the company they don't like that they
have to double-click on the cell to activate the combo box.

How can we show the combo box as soon as they enter the cell?
Any ideas?

Regards
Rene

Drop down list with search-as-you-type behaviour
 

You would modify the SelectionChange event to perform both the action of
hiding the combobox if it is appropriate or displaying the combobox if that
is appropriate.

--
Regards,
Tom Ogilvy

"Rene H" wrote in message
...
Hello,

We have to provide a drop down list in a worksheet and have implemented
the
feature as described in http://www.contextures.com/xlDataVal11.html. Works
ok
so far and fulfils the requirement of searching a matching entry as you
type.

As we have very demanding people in the company they don't like that they
have to double-click on the cell to activate the combo box.

How can we show the combo box as soon as they enter the cell?
Any ideas?

Regards
Rene

Drop down list with search-as-you-type behaviour
 

You could put all the code into the Worksheet_SelectionChange event, e.g.:

'==============================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("ValidationLists")
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
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 = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
Else
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub
'====================

Rene H wrote:
Hello,

We have to provide a drop down list in a worksheet and have implemented the
feature as described in http://www.contextures.com/xlDataVal11.html. Works ok
so far and fulfils the requirement of searching a matching entry as you type.

As we have very demanding people in the company they don't like that they
have to double-click on the cell to activate the combo box.

How can we show the combo box as soon as they enter the cell?
Any ideas?

Regards
Rene



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Drop down list with search-as-you-type behaviour
 

Thanks, I'll try this and let you know.

"Debra Dalgleish" wrote:

You could put all the code into the Worksheet_SelectionChange event, e.g.:

'==============================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("ValidationLists")
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
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 = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
Else
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub
'====================

Rene H wrote:
Hello,

We have to provide a drop down list in a worksheet and have implemented the
feature as described in http://www.contextures.com/xlDataVal11.html. Works ok
so far and fulfils the requirement of searching a matching entry as you type.

As we have very demanding people in the company they don't like that they
have to double-click on the cell to activate the combo box.

How can we show the combo box as soon as they enter the cell?
Any ideas?

Regards
Rene



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html