Dependent Listbox.

Need to have named ranges for each box. Bit of a fiddly job. Here is "something I prepared earlier" that hopefully helps.

http://www.mrexcel.com/forum/showthread.php?t=294021

thanks Brain,

i have done it using a function and sub. Please note you need to array here otherwise if named range contains only one cell, the sub would through error.

Hope somebody find it useful.

Thanks again Brain for looking in to this.


and CellInNamedRange function is :

I imagine you have a good reason for using a programmatic solution when Excel-native solutions exist...

See Debra Dalgleish's solution at http://www.contextures.com/xldataval02.html and my solution that works with data in a table at
Cascading queries
http://www.tushar-mehta.com/excel/newsgroups/cascading_dropdowns/index.html

G2K said:

thanks Brain,

i have done it using a function and sub. Please note you need to array here otherwise if named range contains only one cell, the sub would through error.

Hope somebody find it useful.

Thanks again Brain for looking in to this.

Code:

Public Sub List1()
  Dim lb As msforms.ListBox
    Dim rcArray() As Variant
    Dim lrw As Long, lcol As Long
    Dim rngTarget As Range
 
    If Sheets(1).Range("E20") = "" Then MsgBox "Please Select Industry Name To Continue", vbInformation, "Error": Exit Sub
    If IsEmpty(CellInNamedRange(Sheets(1).Range("E20"))) Then MsgBox "Range Define Error," & vbCrLf & "" & vbCrLf & "" & vbCrLf & "'Please Contact System Administrator", vbCritical, "Logical Error"
    On Error Resume Next
    Set rngTarget = Range(NamedRange(Sheets(1).Range("E20")))
    If rngTarget Is Nothing Then MsgBox "Please select industry from available options!", vbCritical, "Logical Error": Exit Sub
 
    'Set the boundaries of the array
    ReDim Preserve rcArray(1 To rngTarget.Rows.Count, 1 To rngTarget.Columns.Count)
 
    'Fill the array with data from the worksheet
    With rngTarget
        For lcol = 1 To .Columns.Count
            For lrw = 1 To .Rows.Count
                rcArray(lrw, lcol) = rngTarget.Cells(lrw, lcol)
            Next lrw
        Next lcol
    End With
 
    'Place the array in the listbox
    Set lb = Msform.ListBox
    With lb
        .List = rcArray
    End With
    Msform.Caption = "Sub Industry"
    Msform.Show
End Sub

and CellInNamedRange function is :

Code:

Public Function CellInNamedRange(rng As Range) As String
    Select Case Range("E19")
        Case "Marketing"
            CellInNamedRange = ThisWorkbook.names("Marketing")
        Case "Human Resource"
            CellInNamedRange = ThisWorkbook.names("Human_Resource")
        Case "Facilities Management"
            CellInNamedRange = ThisWorkbook.names("Facilities_Management")
        Case "Global Operations"
            CellInNamedRange = ThisWorkbook.names("Global_Operations")
    End Select
End Function

Click to expand...

Thanks Tushar,

I knew about these methods and have seen many post related to this.
The main problem was I wanted to show the values of "Named Range" in "List Box" not in "Combo Box" where user could select multiple options at a time and unfortunately I could not find any post for this.

The next solution - "Ms Query" is the best way to get this done i belive but unfortunately I am working in network environment where Ms office is not in my local drive and it does not support many features of ms-excel. Request you to guide me if there is better solution of this problem with limited resources.
Also, I have read hundreds of your post on internet and learnt alot. i genuinely belive you are a “Excel” geek and real “genius”

Thanks again for your time.