MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VBA Excel Drop down lists

  Asked By: Lucina    Date: Oct 17    Category: MS Office    Views: 2365

I have looked and not found a limit to list option in the combo box
property list of the form.

Matchrequired is set to yes but the user is still able to type another
value in the combo box. What I am trying to achieve is that the user
should not be able to type anything, only select from the drop down

The showmodal property of the form = true but that does not help.



4 Answers Found

Answer #1    Answered By: Eileen Carr     Answered On: Oct 17

if MatchRequired is set  to True and the user  types a value that is not
available in the list, not an Error that interrupts the script is generated but
a message which gives the user the opportunity to correct the input.

If this is not good enough, you can use:

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
MsgBox "Please pick a choice by click the small arrow."
End Sub


But if the users keeps on giving Enter, still the message will appear.

Why don't you use a ListBox instead?

Answer #2    Answered By: Phoebe Brown     Answered On: Oct 17

Thank you for your solution. Maybe I am being a bit pedantic. I
would like the combo  box to function like a calendar control. i.e.
when the user  clicks on the arrow, all he is able to do is to make a
selection and nothing else. Is there a solution for this type  of
situation? The list  box is not an option  for what the user requires.
This user is very very very specific.

Answer #3    Answered By: Latoya Murray     Answered On: Oct 17

Would it be an option  to have another userform with a fully expanded list  box
on it. On the main user form  have a text box  for the value and open the
second userform when the user  goes to the textbox. This would seem almost
like clicking the arrow.

Answer #4    Answered By: Shobhana R.     Answered On: Oct 17

What is the range of dates that you require to display?
If the range is not too great you maybe able to adapt the following.

I have a rolling roster program which uses a list  for the source for a
combo box  to select  the month.
We only need to show 7 months past, the current month & 13 months in the
This is accomplished by row 8 of the list being set  to:
The earlier months are set to the above formula with a month setting:
Month(Now()-1) etc.
The later months are set to the above formula with a month setting:
Month(Now()+1) etc.

Have never had the need to go down to actual days, but think that could
be done if the range is not too great, or not desirable but could be
accomplished using multiple combo's for Day, Month, Year and then some
form of concatenation.

Didn't find what you were looking for? Find more on VBA Excel Drop down lists Or get search suggestion and latest updates.