Code for Input Box

L

Laura C

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
 
F

Fred

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
 
L

Laura C

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 are:
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?
 
D

Dick Kusleika

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?
Click to expand...

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.
Click to expand...

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].
Click to expand...

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

Laura C

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?
Click to expand...

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.
Click to expand...

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].
Click to expand...

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


.
Click to expand...
 
Top