Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem With Drop Down Box | Excel Discussion (Misc queries) | |||
problem with drop down lists | Excel Discussion (Misc queries) | |||
Drop Down Box problem | New Users to Excel | |||
Drop-down list problem | Excel Discussion (Misc queries) | |||
Drop down box problem | Excel Programming |