how to make forms combo box jump to to of list

[JUDI]

I finally figured out how to make a forms combo box and
fill it with data. Once I select an item in the drop down
list though, the next time you go to select and item, the
top of the list is the item last selected not the top of
the list. I have to scroll up the list using the arrow
key to get to the top to the list. Also, if I type in the
first letter, nothing happens.
How do I make my combo box default to the top of the list
when a user clicks the arrow on the right of the combo
box and also how do I make the combo box react to typing
in the first letter or letters?
I can't find this info anywhere - sure hope someone can
help.

 

[Bob Phillips]

Judi,

To reset the combobox back to the top, set .ListIndex to 0 after you have
processed the value.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Judi]

Thanks for responding but where do I do this?

-----Original Message-----
Judi,

To reset the combobox back to the top, set .ListIndex to 0 after you have
processed the value.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.

 

[Bob Phillips]

Right after the code that gets the value from the combobox.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Judi]

I appreciate your help and I'm sorry to be so dumb - but
I don't understand what you mean. Is there a way to be
able to see the properties of the combo box and put that
info in other than going into VB?

I thought I saw it once when I selected the combo box,
then right clicked on it and selected Properties but now
when I try that, I don't get that option - just the
Format Control.

If I click View, Toolbars, Forms and then click the Edit
Code icon, it takes me into VB and I'm lost in there. I
tried pasting your set .ListIndex to 0 in there but it
didn't work of course. As you noticed, I suck at
programming but I really need this to work for me.

 

[Debra Dalgleish]

You've used a combobox from the Forms Toolbar. What Bob is describing,
and the behaviour that you want, is in a combobox from the Control Toolbox.

 

[pfsardella]

I'm not sure what you're trying to do with your Drop Down, but here
are a few snippets that may help. AFAIK, the Drop Downs from the Forms
Toolbar do not allow the second option that you request.


Sub DropDown1_Change()
Dim drpdwn As DropDown
Set drpdwn = ActiveSheet.DropDowns(Application.Caller)
With drpdwn
ActiveSheet.Cells(1, 1).Value = .List(.ListIndex)
End With
End Sub

Sub FindMe()
Dim drpD As DropDown
Dim rngF As Range
Dim strL As String
Set drpD = ThisWorkbook.Sheets(1).DropDowns("Drop Down 1")
With drpD
strL = .List(.ListIndex)
.ListIndex = 1 ' Sets to first choice. Set to 0 for none.
End With
Set rngF = ThisWorkbook.Sheets(1).Cells.Find(What:=strL, _
Lookat:=xlWhole, LookIn:=xlFormulas)
If Not rngF Is Nothing Then
rngF.Select ' Or whatever.
End If
End Sub

HTH
Paul

 

[Judi]

Thank-you. I managed to make my Forms Combo Box jump to
the top in the list when selected. Are you saying that in
order for me to make it go to the right item in the list
when typing in a letter, that I have to use a Control
Combo Box?
I tried making a Control Combo Box somewhat the same way
as a Forms Combo Box (select the list of items,
F3 ..Select a blank cel, F3 etc)
When I right click the combo box after this and select
Properties, I can't find anything that says
set .ListIndex . Does this also let you be able to type
a letter?
Your help is much appreciated.

 

[Bob Phillips]

Judi,

Yes, to get the type-in functionality you want, you need a Control Toolbox
Combobox.

You won't see anything that says Set .Listindex, but you should see a
property called ListIndex, which identifies which item is selected. The
first item is 0, if you input -1 it starts with a blank, nothing selected.
With the controls toolbox combo you have a much richer set of properties. To
get at these you need to go into design mode (click the blue-green triangle
on the controls toolbox toolbar), select the combobox, and then click the
properties icon on the same toolbar, this will give you access to those
properties.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Judi]

Thanks so much for the help. I managed to make a control
combo box and filled it with data but when I right click
on it and select properties, I don't see Listindex
anywhere. I lets me select from the drop down box but if I
type a letter, the drop down arrow disappears and just the
letter I typed remains in the cell.????
I don't know what I did wrong because the first time I
followed your instructions - it worked properly and I
didn't even change Listindex as I couldn't find it.

:-(