Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search with drop down list as answers | Excel Discussion (Misc queries) | |||
No drop down list type-ahead? | Excel Discussion (Misc queries) | |||
drop down list that i can also type regular text in as well | Excel Programming | |||
How do I increase type size in an Excel drop down list? | Excel Discussion (Misc queries) | |||
search with drop down list | Excel Worksheet Functions |