Is there a way to programmatically retract the dropdown list for a combobox
on a form?

See VBA help

ShowDropButtonWhen Property

fmShowDropButtonWhenNever 0 Do not show the drop-down button under
anycircumstances.
fmShowDropButtonWhenFocus 1 Show the drop-down button when the control has
the focus.
fmShowDropButtonWhenAlways 2 Always show the drop-down button.


"joeeng" wrote:

Is there a way to programmatically retract the dropdown list for a combobox
on a form?

Thanks, Joel
However, this is not quite what I am looking for. I would like to hide
(retract) the dropdown list itself (not the button) when the user types
something in the combobox that does not match anything in the dropdown list.
I have worked out the procedure, but I cannot figure out how to get the
dropdown list to hide itself. Dropdown method makes it appear, but what
makes it retract?
"Joel" wrote:

See VBA help

ShowDropButtonWhen Property

fmShowDropButtonWhenNever 0 Do not show the drop-down button under
anycircumstances.
fmShowDropButtonWhenFocus 1 Show the drop-down button when the control has
the focus.
fmShowDropButtonWhenAlways 2 Always show the drop-down button.


"joeeng" wrote:

Is there a way to programmatically retract the dropdown list for a combobox
on a form?

What about disable the combobox? You can check the properties window in the
userform (select combobox) to see all the options. You can change them
manual in the property window so you can see the effectr of each change
before adding code to perform same operation. If the property window doesn't
display the go to menu View to add the window.

"joeeng" wrote:

Thanks, Joel
However, this is not quite what I am looking for. I would like to hide
(retract) the dropdown list itself (not the button) when the user types
something in the combobox that does not match anything in the dropdown list.
I have worked out the procedure, but I cannot figure out how to get the
dropdown list to hide itself. Dropdown method makes it appear, but what
makes it retract?
"Joel" wrote:

See VBA help

ShowDropButtonWhen Property

fmShowDropButtonWhenNever 0 Do not show the drop-down button under
anycircumstances.
fmShowDropButtonWhenFocus 1 Show the drop-down button when the control has
the focus.
fmShowDropButtonWhenAlways 2 Always show the drop-down button.


"joeeng" wrote:

Is there a way to programmatically retract the dropdown list for a combobox
on a form?

Joel,
Based on your idea, a simple workaround worked.

Combobox.Enabled=False
Combobox.Enabled=True
Combobox.SetFocus

This hides the dropdown list and returns to the combobox exactly where the
user left off typing the new entry.

Thanks for the idea.

"Joel" wrote:

What about disable the combobox? You can check the properties window in the
userform (select combobox) to see all the options. You can change them
manual in the property window so you can see the effectr of each change
before adding code to perform same operation. If the property window doesn't
display the go to menu View to add the window.

"joeeng" wrote:

Thanks, Joel
However, this is not quite what I am looking for. I would like to hide
(retract) the dropdown list itself (not the button) when the user types
something in the combobox that does not match anything in the dropdown list.
I have worked out the procedure, but I cannot figure out how to get the
dropdown list to hide itself. Dropdown method makes it appear, but what
makes it retract?
"Joel" wrote:

See VBA help

ShowDropButtonWhen Property

fmShowDropButtonWhenNever 0 Do not show the drop-down button under
anycircumstances.
fmShowDropButtonWhenFocus 1 Show the drop-down button when the control has
the focus.
fmShowDropButtonWhenAlways 2 Always show the drop-down button.


"joeeng" wrote:

Is there a way to programmatically retract the dropdown list for a combobox
on a form?

I finally remembered the right way to do it. set index number to -1.

"joeeng" wrote:

Joel,
Based on your idea, a simple workaround worked.

Combobox.Enabled=False
Combobox.Enabled=True
Combobox.SetFocus

This hides the dropdown list and returns to the combobox exactly where the
user left off typing the new entry.

Thanks for the idea.

"Joel" wrote:

What about disable the combobox? You can check the properties window in the
userform (select combobox) to see all the options. You can change them
manual in the property window so you can see the effectr of each change
before adding code to perform same operation. If the property window doesn't
display the go to menu View to add the window.

"joeeng" wrote:

Thanks, Joel
However, this is not quite what I am looking for. I would like to hide
(retract) the dropdown list itself (not the button) when the user types
something in the combobox that does not match anything in the dropdown list.
I have worked out the procedure, but I cannot figure out how to get the
dropdown list to hide itself. Dropdown method makes it appear, but what
makes it retract?
"Joel" wrote:

See VBA help

ShowDropButtonWhen Property

fmShowDropButtonWhenNever 0 Do not show the drop-down button under
anycircumstances.
fmShowDropButtonWhenFocus 1 Show the drop-down button when the control has
the focus.
fmShowDropButtonWhenAlways 2 Always show the drop-down button.


"joeeng" wrote:

Is there a way to programmatically retract the dropdown list for a combobox
on a form?

I tried setting listindex to -1 and this is what I found. Setting the
listindex to -1 merely removes the selection focus from the dropdown. It
does not hide the dropdown list.

"Joel" wrote:

I finally remembered the right way to do it. set index number to -1.

"joeeng" wrote:

Joel,
Based on your idea, a simple workaround worked.

Combobox.Enabled=False
Combobox.Enabled=True
Combobox.SetFocus

This hides the dropdown list and returns to the combobox exactly where the
user left off typing the new entry.

Thanks for the idea.

"Joel" wrote:

What about disable the combobox? You can check the properties window in the
userform (select combobox) to see all the options. You can change them
manual in the property window so you can see the effectr of each change
before adding code to perform same operation. If the property window doesn't
display the go to menu View to add the window.

"joeeng" wrote:

Thanks, Joel
However, this is not quite what I am looking for. I would like to hide
(retract) the dropdown list itself (not the button) when the user types
something in the combobox that does not match anything in the dropdown list.
I have worked out the procedure, but I cannot figure out how to get the
dropdown list to hide itself. Dropdown method makes it appear, but what
makes it retract?
"Joel" wrote:

See VBA help

ShowDropButtonWhen Property

fmShowDropButtonWhenNever 0 Do not show the drop-down button under
anycircumstances.
fmShowDropButtonWhenFocus 1 Show the drop-down button when the control has
the focus.
fmShowDropButtonWhenAlways 2 Always show the drop-down button.


"joeeng" wrote:

Is there a way to programmatically retract the dropdown list for a combobox
on a form?