Hello.

Help please?
I asked a rather long winded question yesterday about
Combo Boxes. What I think I'm going to have to try and do
is use an input box. Can anyone offer any code on how to
have a combo box or list within an input box. The user
must select a category which is then filtered for viewing.
So I don't want them typing anything but selecting? Is it
possible?

Cheers
Laura

Hi Laura,

to my knowledge you cannot make a standard inputbox behave like a combobox.
I would use a user form with a combobox instead. I don't know what your
problems were with comboboxes yesterday, but this seems the only option to
me.

Good Luck


"Laura C" wrote in message
...
Hello.

Help please?
I asked a rather long winded question yesterday about
Combo Boxes. What I think I'm going to have to try and do
is use an input box. Can anyone offer any code on how to
have a combo box or list within an input box. The user
must select a category which is then filtered for viewing.
So I don't want them typing anything but selecting? Is it
possible?

Cheers
Laura

Fred,
Cheers but I think that will just throw up the problem I
had yesterday this is an edited version, don't know if you
can offer any help... as I'm pretty stuck not having used
VB for very long.

What I am try to do:
Populate a list for a combo box. The drop down is on a
front sheet (not a userform) and on pressing it I want it
to filter the data on another worksheet (to have unique
values) and populate the list with a column of the
filtered data. The user will then select a category from
the list, the value selected will be used to refilter the
data on the other sheet to display records for that
category only. This will then be copied to another sheet
which is used as source data for a chart. This is
displayed to the user, I would then like all data to
return to unfiltered state.

I am happy with my filtering/ copying code however I'm
stuck with the Combo/List code
I am using
Sub ComboBox1_DropButtonClick()
My difficulties a
1/ The code is run not only on pressing of the dropbutton
but also on selection of a value from the list. What can I
use to only run it once?
2/ I would like the list do be displayed mid sub so the
value can be selected and used straight away (then I can
continue with the code so the user doesn't have to press
anything else i.e. ShowAllData then refilter for the
category) Currently the list is displayed after the End
Sub.
3/ I want to use the Cells(x,y) notation rather than Range
("Data!C2:C1000") so that the the list doesn't contain a
lot of blank rows i.e.
Sheets("Front_End").ComboBox1.ListFillRange = Sheets
("Data").Range(Cells(2, 3), Cells(Rownumber, 3)) this
gives an Run-time error 1004 'Application or Object
Defined Error'[Rownumber has just counted the rows which
contain data from filtered list].

Or are there a better way to do this?


-----Original Message-----
Hi Laura,

to my knowledge you cannot make a standard inputbox
behave like a combobox.
I would use a user form with a combobox instead. I don't
know what your
problems were with comboboxes yesterday, but this seems
the only option to
me.

Good Luck


"Laura C" wrote in
message
...
Hello.

Help please?
I asked a rather long winded question yesterday about
Combo Boxes. What I think I'm going to have to try and
do
is use an input box. Can anyone offer any code on how to
have a combo box or list within an input box. The user
must select a category which is then filtered for
viewing.
So I don't want them typing anything but selecting? Is
it
possible?

Cheers
Laura


.

Laura


1/ The code is run not only on pressing of the dropbutton
but also on selection of a value from the list. What can I
use to only run it once?

Use the GotFocus event instead of the click event.

2/ I would like the list do be displayed mid sub so the
value can be selected and used straight away (then I can
continue with the code so the user doesn't have to press
anything else i.e. ShowAllData then refilter for the
category) Currently the list is displayed after the End
Sub.

You can't pause you macro in this way. You need to think of it as
event-driven. What events are happening and what code do you want to run
when they happen. It sounds to me like you want to fill the combobox with
the GotFocus event, then when the user the selects something from the
combobox (the Change event), do your filtering and copying.

3/ I want to use the Cells(x,y) notation rather than Range
("Data!C2:C1000") so that the the list doesn't contain a
lot of blank rows i.e.
Sheets("Front_End").ComboBox1.ListFillRange = Sheets
("Data").Range(Cells(2, 3), Cells(Rownumber, 3)) this
gives an Run-time error 1004 'Application or Object
Defined Error'[Rownumber has just counted the rows which
contain data from filtered list].

I would use the AddItem method to fill the combobox instead of the
ListFillRange. Here's an example that populates a listbox on a userform
with unique values.

http://www.dicks-blog.com/excel/2004...collectio.html

It will work pretty much the same for a combobox on a sheet. It uses a
collection object to create a unique list from a range, then puts the
collection items in the combobox.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Thank you heaps, it's well on it's way to working!


-----Original Message-----
Laura


1/ The code is run not only on pressing of the
dropbutton
but also on selection of a value from the list. What
can I
use to only run it once?

Use the GotFocus event instead of the click event.

2/ I would like the list do be displayed mid sub so the
value can be selected and used straight away (then I can
continue with the code so the user doesn't have to press
anything else i.e. ShowAllData then refilter for the
category) Currently the list is displayed after the End
Sub.

You can't pause you macro in this way. You need to think
of it as
event-driven. What events are happening and what code do
you want to run
when they happen. It sounds to me like you want to fill
the combobox with
the GotFocus event, then when the user the selects
something from the
combobox (the Change event), do your filtering and
copying.

3/ I want to use the Cells(x,y) notation rather than
Range
("Data!C2:C1000") so that the the list doesn't contain a
lot of blank rows i.e.
Sheets("Front_End").ComboBox1.ListFillRange = Sheets
("Data").Range(Cells(2, 3), Cells(Rownumber, 3)) this
gives an Run-time error 1004 'Application or Object
Defined Error'[Rownumber has just counted the rows which
contain data from filtered list].

I would use the AddItem method to fill the combobox
instead of the
ListFillRange. Here's an example that populates a
listbox on a userform
with unique values.

http://www.dicks-
blog.com/excel/2004/05/using_collectio.html

It will work pretty much the same for a combobox on a
sheet. It uses a
collection object to create a unique list from a range,
then puts the
collection items in the combobox.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


.