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?

--
Ian
--

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" wrote in message news:bU_Un.51524$k15.28828@hurricane...
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?

--
Ian
--

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.

--
Ian
--

"MerseyBeat" wrote in message
...
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" wrote in message
news:bU_Un.51524$k15.28828@hurricane...
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?

--
Ian
--

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.



On 06/25/2010 04:44, IanC wrote:
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?

--
Ian
--



--
Dave Peterson

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.

--
Ian
--

"Dave Peterson" wrote in message
...
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.



On 06/25/2010 04:44, IanC wrote:
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?

--
Ian
--



--
Dave Peterson

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

You may want to try again or post more details.



On 06/25/2010 06:28, IanC wrote:
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.

--
Ian
--

"Dave wrote in message
...
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.



On 06/25/2010 04:44, IanC wrote:
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?

--
Ian
--



--
Dave Peterson



--
Dave Peterson

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.

--
Ian
--

"Dave Peterson" wrote in message
...
I've never seen that .text property break using this.

You may want to try again or post more details.



On 06/25/2010 06:28, IanC wrote:
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.

--
Ian
--

"Dave wrote in message
...
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.



On 06/25/2010 04:44, IanC wrote:
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?

--
Ian
--



--
Dave Peterson



--
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.

On 06/25/2010 08:32, IanC wrote:
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.

--
Ian
--

"Dave wrote in message
...
I've never seen that .text property break using this.

You may want to try again or post more details.



On 06/25/2010 06:28, IanC wrote:
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.

--
Ian
--

"Dave wrote in message
...
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.



On 06/25/2010 04:44, IanC wrote:
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?

--
Ian
--



--
Dave Peterson



--
Dave Peterson




--
Dave Peterson