Locking down a combobox

[IanC]

Is there a way to allow a combobox entry to be selected from a list, but to
disable any option to enter text manually?

I know I can set the MatchRequired property to ensure that any text must
match an item in the list, but this still allows typing to be done. I
suspect I could use a ListBox, but I want the drop-down list to appear.

Any thoughts?

 

[MerseyBeat]

Ian,

A Listbox is the solution. What do you mean "I want the drop-down to
appear" ? A Listbox will show the dropdown list.

Please elaborate.

MB

 

[IanC]

Hi MerseyBeat

Thanks for the quick response.

As far as I understand it, you can scroll up/down through the contents of
the list in a listbox, but there's no way to display the whole list to click
on an entry without expanding the size of the listbox. A combobox has a
drop-down arrow to the right of the text area which does this.

In the help for ListBox Control it says "You can't drop text into a
drop-down ListBox" but I can't find any way to make it drop down.

Using Excel 2000.

 

[Dave Peterson]

Is this a combobox from the Control toolbox toolbar (if it's on a a worksheet)
or a combobox on a userform (inside the VBE)?

If yes, then look at the .Style property of that combobox.

 

[IanC]

Thanks Dave. fmStyleDropDownList appears to be just what I needed.

The only drawback is that I need to rewrite some of my code. When I select
this, the Text property becomes invalid. I currently have "Select" appearing
in each ComboBox to prompt the user and some code that looks for the absence
of this to enable a command button (ie each ComboBox must have an option
selected for the OK button to be enabled).

I'm sure I'll find a way round it.

 

[Dave Peterson]

I've never seen that .text property break using this.

You may want to try again or post more details.

 

[IanC]

Hi Dave

I'm not trying to change the Text property in code, but in the Properties
box in VBE.

Initailly, I had
Style = 0 - fmStyleDropDownCombo
Text = Select

When I changed Style to 2 - fmStyleDropDownList, Text automatically cleared.
Now when I try to enter something in Text, I get "Could not set the Text
property. Invalid property value."

If I copy/paste an entry from the list this is accepted, but if I try to
type anything in (even something in the list) I get the same failure.
MatchEntry is set to 2 - fmMatchEntryNone and MatchRequired is set to False.

Any ideas?

I think I've figured out a workaround for my code if I have to change it. As
an alternative to looking for the absence of "Select" with...
If Me.ComboBox1.Text = "Select" Then Me.CommandButton1.Enabled = False
I think I can set MatchRequired to True, then check the MatchFound property
with ...
If Me.ComboBox1.MatchFound = True Then Me.CommandButton1.Enabled = False

That said, if I use MatchRequired/MatchFound and change my code to suit, I
don't need to change the ComboBox Style as invalid entries will result in
the OK button being disabled.

 

[Dave Peterson]

If "Select" is an option in the combobox list, then you should be able to use
something like:

Option Explicit
Private Sub UserForm_Initialize()
Dim iCtr As Long
With Me.ComboBox1
.Style = fmStyleDropDownList
'first item
.AddItem "(Select)"
'test data
For iCtr = 1 To 5
.AddItem "A" & iCtr
Next iCtr
'show that it works
.Text = "(Select)"
'or even use the top choice
'.ListIndex = 0
End With
End Sub

Personally, I'd modify all those settings via code. Then if (when!) I need the
same kind of form in another project, I can just copy the code and not have to
worry.

Hi Dave

I'm not trying to change the Text property in code, but in the Properties
box in VBE.

Initailly, I had
Style = 0 - fmStyleDropDownCombo
Text = Select

When I changed Style to 2 - fmStyleDropDownList, Text automatically cleared.
Now when I try to enter something in Text, I get "Could not set the Text
property. Invalid property value."

If I copy/paste an entry from the list this is accepted, but if I try to
type anything in (even something in the list) I get the same failure.
MatchEntry is set to 2 - fmMatchEntryNone and MatchRequired is set to False.

Any ideas?

I think I've figured out a workaround for my code if I have to change it. As
an alternative to looking for the absence of "Select" with...
If Me.ComboBox1.Text = "Select" Then Me.CommandButton1.Enabled = False
I think I can set MatchRequired to True, then check the MatchFound property
with ...
If Me.ComboBox1.MatchFound = True Then Me.CommandButton1.Enabled = False

That said, if I use MatchRequired/MatchFound and change my code to suit, I
don't need to change the ComboBox Style as invalid entries will result in
the OK button being disabled.

 

[IanC]

Hi Dave

"Select" is just the initial setting for the ComboBoxes. It isn't in the
list. The intention is to remind the user what they need to do, but not to
be a selectable item.

I've been looking for a way to delete "Select" from the list as soon as
another option is highlighted, but can't figure it out. I know it will
require RemoveItem, but I two issues with this:
1. How do I work out how to determine whether "Select" is in the focus
2. How do I delete "Select" by name.

--
Ian
--

If "Select" is an option in the combobox list, then you should be able to
use something like:

Option Explicit
Private Sub UserForm_Initialize()
Dim iCtr As Long
With Me.ComboBox1
.Style = fmStyleDropDownList
'first item
.AddItem "(Select)"
'test data
For iCtr = 1 To 5
.AddItem "A" & iCtr
Next iCtr
'show that it works
.Text = "(Select)"
'or even use the top choice
'.ListIndex = 0
End With
End Sub

Personally, I'd modify all those settings via code. Then if (when!) I
need the same kind of form in another project, I can just copy the code
and not have to worry.

 

[Dave Peterson]

If "(Select)" isn't part of the list, then how do you get it to show up in the
combobox choices?

I think it would be simpler to add a label with an instruction that told the
user to select their choices.

(Actually, I can't imagine anyone needing to be told how to use the comboboxes.
With all the stuff on the web, it's pretty obvious what needs to be done.)

Anyway, this worked ok for me:

Option Explicit
Dim BlkProc As Boolean
Private Sub ComboBox1_Change()

Dim myList As Variant
Dim myVal As String
Dim iCtr As Long

If BlkProc = True Then
Exit Sub
End If

With Me.ComboBox1
If .ListIndex < 0 Then
Exit Sub 'nothing chosen
End If

If LCase(.List(0, 0)) = LCase("(Select)") Then
myList = .List
myVal = .Value
BlkProc = True
.Clear
'Skip the first entry
For iCtr = 1 To UBound(myList, 1)
.AddItem myList(iCtr, 0)
Next iCtr
.Value = myVal
BlkProc = False
End If

End With

End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
BlkProc = True
With Me.ComboBox1
.Style = fmStyleDropDownList
'first item
.AddItem "(Select)"
'test data
For iCtr = 1 To 5
.AddItem "A" & iCtr
Next iCtr
'show that it works
.Text = "(Select)"
'or
.ListIndex = 0
End With
BlkProc = False
End Sub

 

[IanC]

Hi Dave

If "(Select)" isn't part of the list, then how do you get it to show up in
the combobox choices?

Me.ComboBox1.Text = "Select"

This only works as long as the Locked property is set to False. Instead of
using Locked, I've set MatchRequired to True and use the following lines to
enable/disable the OK button. There are several ComboBoxes so I set the
button to Enabled then check each ComboBox. Any one returning false will
disable the button.

CommandButton1.Enabled = True
If Me.ComboBox1.MatchFound = False Then _
Me.CommandButton1.Enabled = False

Anyway, this worked ok for me:

---code snipped---

Your code worked OK on it's own, but I couldn't work out how to make it look
for a list in another worksheet. It's probably pretty simple, but then
again, so am I ;-)

The solution I'm using is working OK apart from one minor glitch which I'm
not too worried about ("Invalid property value" appears if I attempt to
close the form without selecting from each of the comboboxes). I haven't
figured out a way of trapping this, but it's a minor detail as the users
shouldn't be closing the forms incomplete anyway.