Put results of a query in a drop down list

[t16turbo]

Hello,

I have a query on a worksheet named "Managers"
this returns a single column of project managers.

I need to put the results of this in a drop down for people to select.
this will then get passed into another query.

Also, The list will need refreshing when the Spreadsheet opens.

any ideas?
thanks in advance..

 

[xlbo]

set the query to run on file open (one of the properties of the data table)

set up a dynamic named range to hold the returned results

Reference this named range in Data>Validation to create your dropdown list

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[t16turbo]

I have now created the list box and, with your help, define the named range.

I have a command button which, when clicked, calls my Module1 code and passes in the value of the listbox.

this is where I am having an error.

my code on the sheet with the list box and command button is:

Private Sub CommandButton1_Click()

ManagerName = ListBox1.Value

Call AutoFOB(ManagerName)

End Sub

when I click this, I get the error "Wrong number of arguments"
I'm not sure why this is..
do you know?

 

[xlbo]

I ain't psychic so I guess I would need to know whether AutoFOB requires a parameter to be passed to it ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[t16turbo]

you're not?! ;-)

AutoFob does have a variable in it, that was originally obtained via a FOR loop on a range of data.

What the problem turned out to be was the listbox value going straight into the 'Call' argument.

Instead I included a textbox, and in the ListBox1_Click() behaviour set the text box.

then in the command button I just pass the value of the textbox into the main Sub