Re: Code for Input Box

From: Laura C (anonymous_at_discussions.microsoft.com)
Date: 06/29/04 

Date: Tue, 29 Jun 2004 03:09:54 -0700

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 *** (not a userform) and on pressing it I want it
to filter the data on another work*** (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 *** to display records for that
category only. This will then be copied to another ***
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?

>-----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" <anonymous@discussions.microsoft.com> wrote in
message
>news:22d3701c45dbb$2d437680$a501280a@phx.gbl...
>> 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
>
>
>.
>