drop down problem
 

I would like to create a drop down on my form to list each sheet name.

I realise to do this I could create a list on a seperate sheet and make it a
named range containing all the sheet names

Is it possible to add to this and to have another drop down underneath that
which would take the sheet name selected and use values in a dynamic range
on the sheet name selected as entries to the second drop down (like a
conditional drop down based on the first choice)

So if the sheet name selected is sheet1 and all the possible sheets to
select have a dynamic
range in row 1 columns B:? (? being the last column in the range covered
dynamically)

The dynamic range would have to applicable to each sheet found in the first
drop down list not a named dynamic range on each sheet.

Is this possible

Thanks

Rob

drop down problem
 

Here is some code I use with a list box on a worksheet.

For the second part - you can set the ListBox1_Exit event
to populate the second box. The easiest way is to set the
rowsource propert of the 2nd listbox. You should be able to use
range names.

You can adapt it to the initialize event of the form.

Private Sub Worksheet_Activate()
Dim cell As Range, ws As Worksheet
Application.ScreenUpdating = False
Dim x As Integer
With ActiveSheet
.ListBox1.Clear
x = 1
For Each ws In ActiveWorkbook.Worksheets

If ws.Name < "Index" Then
.ListBox1.AddItem ws.Name
End If
Next
End With
Range("a1").Select
Application.ScreenUpdating = True
End Sub

--
steveB

Remove "AYN" from email to respond
"Rob Hargreaves" wrote in message
...
I would like to create a drop down on my form to list each sheet name.

I realise to do this I could create a list on a seperate sheet and make it
a named range containing all the sheet names

Is it possible to add to this and to have another drop down underneath
that
which would take the sheet name selected and use values in a dynamic range
on the sheet name selected as entries to the second drop down (like a
conditional drop down based on the first choice)

So if the sheet name selected is sheet1 and all the possible sheets to
select have a dynamic
range in row 1 columns B:? (? being the last column in the range covered
dynamically)

The dynamic range would have to applicable to each sheet found in the
first
drop down list not a named dynamic range on each sheet.

Is this possible

Thanks

Rob

drop down problem
 

better explained i hope -

I have a form and a named range supplying the sheet names I would like to
include in its selection as a drop down combo box.

I have another combo on the same form which I would like to give values
conditionally based on the selection in the first combo. It is the source of
the second combo I arent sure if it is possible to do.

Here is where the data lies.

On each sheet listed in the first combo. There is a variable number of
columns in row 2 starting at column B so I need to use a dynamic range.

I know how to do this using a named dynamic range but not a generic? dynamic
range.

For example

the first combo selection is "sheet1"

the second combo contains options from B2, B3 & B4 on the corresponding
sheet (so 3 selections here)

if the user had chosen in the first combo "sheet2"

there are 5 options in B2, B3, B4, B5, & B6

is this possible?

Rob

drop down problem
 

Maybe this is possible in a different way, the long way round!

Since the second combos first value will always be in cell B2 and there will
never be more than 10 values could I specify all 10 combinations (B2:B12)
but leave out any cells with no value.

thanks again

Rob
"Rob Hargreaves" wrote in message
...
better explained i hope -

I have a form and a named range supplying the sheet names I would like to
include in its selection as a drop down combo box.

I have another combo on the same form which I would like to give values
conditionally based on the selection in the first combo. It is the source
of the second combo I arent sure if it is possible to do.

Here is where the data lies.

On each sheet listed in the first combo. There is a variable number of
columns in row 2 starting at column B so I need to use a dynamic range.

I know how to do this using a named dynamic range but not a generic?
dynamic range.

For example

the first combo selection is "sheet1"

the second combo contains options from B2, B3 & B4 on the corresponding
sheet (so 3 selections here)

if the user had chosen in the first combo "sheet2"

there are 5 options in B2, B3, B4, B5, & B6

is this possible?

Rob