ComboBox DropDown Button: ShowDropButtonWhenNever - Button still shows.

[melpa]

On a UserForm I have a very small ComboBox populated with a very short list of numbers.
I don't want a DropDown arrow because it takes up the whole box. I just want the list to show when the ComboBox is entered.

So I have the ShowDropButtonWhen property set to Never and in the ComboBox Enter event I have "ComboBox1.DropDown".

It works fine when I tab through the controls, but if the ComboBox is selected with the mouse the DropDown arrow appears. That's not Never.
(It's the same thing with or without the Enter event.)

What am I doing wrong? How can I make it so the drop down button never shows?

[torachan]

Set ListRows one greater than number of items in list.

probably misunderstood you

[melpa]

I tried your suggestion torachan, but it made no difference.

[Fluff13]

That works for me, do you have any other event code for that combo?

[melpa]

There's no other event code for that combobox. I made a Userform with nothing but what I needed to test this situation.

Do you mean torachan's solution works for you? Or that when you set the ShowDropButtonWhen to Never the dropdown arrow doesn't show when you select the combobox with the mouse?

[Fluff13]

I mean if I set the ShowDropButtonWhen to Never (option 0) i don't see the button, no matter how I select the combo.

[melpa]

It's not working for me. I've tried it out on two computers, same thing. (Both have Excel 2013 installed.)

I've uploaded my test file for anyone who's interested in taking a look? I'd appreciate any help with this.

[melpa]

Since uploading the file I've noticed that if the combobox isn't populated the dropdown arrow never shows - which is the desired case.
Maybe there's no arrow because there's no list, but it got me wondering if the problem is in the way I'm populating the combobox.
Currently I'm using " ComboBox1.List = Array(1, 2, 3, 4) " in the Initialize event of the userform.
So I tried using the AddItem method instead, but that doesn't make any difference either.
Does anyone have any ideas?

[Fluff13]

Change the Style property to 0 from 2

[melpa]

The style is already set to 2 - fmStyleDropDownList.

[Fluff13]

You need to change it to 0.

[melpa]

Sorry, I misread your post. I'll get back to you...

[melpa]

Progress! Thanks Fluff.
Unfortunately however, now the user can type anything at all into the combobox and I want to restrict values entered to only those provided in the dropdown - which is why I had it set to 2.
Is there a way to have both restricted input AND no dropdown arrow?

[Fluff13]

Appears not. Does does seem as though those two options are mutually exclusive for some reason.

[JCabral]

This restrict values entered

Please Login or Register  to view this content.

[melpa]

Fantastic. Thanks JCabral.

Now the user can ONLY select values from the dropdown list.

I haven't learnt about KeyAscii yet, but a quick google search gives me the impression it restricts the use of the keyboard.
Which leads me to my next question:
My list only has four values: 5, 10, 50 and 100.
Are you able to tell me how to restrict the keys available to 1, 5 and 0?
I would like the users to retain the ability to type in the values if they choose.

[JCabral]

Hi

I'm not a VBA connoisseur, but try something like the code below

Please Login or Register  to view this content.

[torachan]

Try the attached, all the settings have been done in the UserForm initialization code.

[melpa]

Thanks JCabral, your solution works a little bit. With your code 1, 5 and 0 are the only keys that the combobox responds to, but the effect of all of them is to select the first number in the list. I can’t type in 10, 50 or 100.
Unfortunately this solution is no longer relevant anyway because I discovered that when I change the Style property to “ 0 – fmStyleDropDownCombo “ even though it’s true that the Drop Button doesn't show when the ComboBox is selected with the mouse, it takes away both of the other requirements: ie. it allows anything to be typed into the combobox and it also turns out that the list doesn't show on entry anymore – ( “ComboBox1.DropDown” doesn’t appear to work).

Thanks torachan, but your attachment isn't working for me either. I can only enter values from the list, which is one requirement, but the list doesn't drop down on entry and the button appears when the combobox when it is selected with the mouse.

So thanks for everyone's input so far, but I still haven't found the solution.

[melpa]

Hi all.

My dilemma is still not solved, but I can articulate it more clearly now after trying out the various solutions that have been offered to me in this thread.

My aim is to have a ComboBox on a UserForm that:
a) Never shows the drop button.
b) Prohibits values other than those in the list supplied.
c) Shows its list on entry (whether tabbed to or selected with cursor / mouse).

I thought this should be straight forward
a) Set ShowDropButtonWhen property to: 2 – fmShowDropButtonWhenNever
b) Set Style property to: 2 – fmStyleDropDownList (to restrict values entered)
c) Put "ComboBox1.DropDown" in the ComboBox1 Enter event

However this doesn't work. When the ComboBox is selected with the mouse the Drop Button shows.


Given that ComboBox properties are right there in plain sight (ie. standard issue - I don’t have to write code to set them), I can't understand why when I select one property it appears to cancel out the other. Surely the dropdown button should NEVER show.
In fact, on the official Microsoft website the description for 0-fmShowButtonWhenNever is “Do not show the drop-down button under any circumstances.”
(https://docs.microsoft.com/en-us/off...nwhen-property).

So, I’m at a loss. I’ve uploaded an updated file illustrating the problem with the continued hope that my dilemma can be solved.

[rorya]

If you set the MatchRequired property to True, they won't be allowed to enter something that isn't in the list.

[melpa]

Hi rorya.
I think that's only relevant if I use the DropDownCombo style, which is no good in this case because it doesn't show the list on entry.
Also, when I tried that out on the file I uploaded it made the DropDownCombo style combobox virtually unusable.

[rorya]

A simple alternative is to validate the entered data by checking that the Listindex property is not -1 (i.e. the entry matches something in the list).

[melpa]

The DropDownCombo style – being the one that needs code to restrict entries – doesn’t automatically show its list when entered even though I have “"ComboBox.DropDown" in the Enter event.

I’m looking for a solution that satisfies all three criteria: Never shows the button, Restricts user entries, Drops list automatically on entry.

[Kyle123]

I'd fake it with a listbox and a textbox, just make the listbox visible when the textbox is entered and set the focus to the listbox. This essentially makes the textbox read-only and its value is set by the listbox

[rorya]

It drops down on entry for me. For only 4 values, you could just use a textbox and spinbutton, which would also make it easier if the user needs to change an entry.

[melpa]

Hi rorya. It's strange that it drops down for you, but not for you. I'll try out you alternative suggestion. Thank you.
Thanks for your suggestion too, Kyle. I'll also explore your idea.