[jeansymolanza]

I'm struggling to fill a drop down list based on user selection in another. Can't find anything related to it online I have a workbook called lookupDept containing the following table:

A   B 
==  ============================== 
BS  Business School 
CD  Design and Technology 
CG  Chemical Engineering 
CM  Chemistry 
CO  Computer Science 
CV  Civil and Building Engineering 

where column A has the defined name deptCode and column B has the defined name deptName. I have a second workbook called lookupModule which contains the following table:

A       B                                                   C 
======  ==================================================  == 
BSA005  Organisational Behaviour                            BS 
BSA007  Skills for Study                                    BS 
CGA001  Fluid Mechanics I MEng & BEng Status                CG 
CGA002  Stagewise Processes                                 CG 
CMA001  Energetics and Rates of Change                      CM 
CMA002  Structure and Reactivity in Inorganic Chemistry     CM 
COA101  Essential Skills for Computing                      CO 
COA107  Logic and Functional Programming                    CO 
CVA001  Communication                                       CV 
CVA002  Fluid Mechanics                                     CV

I'm trying to update cbo_moduleCode on my form to select a range where column A in lookupDept matches column C in lookupModule. This is the code I'm using:

So if the user picks BS - Business School on the form (which is extracted from the lookupDept workbook, I want all the fields containing BS in column C of the lookupModule workbook to selected. This is the code I'm using so far:

Private Sub UserForm_Initialize()      

Dim c_deptCode As Range     
Dim c_deptName As Range      
Dim deptCodes As Variant     
Dim deptNames As Variant      
Dim ws_dept As Worksheet     
Dim ws_misc As Worksheet     

Set ws_dept = Worksheets("lookupDept")     
Set ws_misc = Worksheets("lookupMisc")      

' Assign each range to an array containing the values     
deptCodes = Choose(1, ws_dept.Range("deptCode"))     
deptNames = Choose(1, ws_dept.Range("deptName"))      

' Create deptcode+deptname cbo     
For i = 1 To ws_dept.Range("deptCode").Rows.Count         
CombinedName = deptCodes(i, 1) & " - " & deptNames(i, 1)         
cbo_deptCode.AddItem CombinedName     
Next i  

End Sub 

[BobRodes]

Where is the code that attempts to get the "user selection in another", please?

[jeansymolanza]

BobRodes, on 17 October 2012 - 03:43 PM, said:

Where is the code that attempts to get the "user selection in another", please?

This is the code I am using right now. It relies on auto filter but it doesn't work when there is only one match on the lookupModule table.

Private Sub cbo_deptCode_Change()
Dim lLoop As Long, rgLoop As range

For lLoop = 1 To Me.cbo_moduleCode.ListCount

    Me.cbo_moduleCode.RemoveItem 0

Next lLoop

Sheets("lookupModule").[a1].CurrentRegion.AutoFilter
Sheets("lookupModule").[a1].CurrentRegion.AutoFilter Field:=3, Criteria1:=Left(Me.cbo_deptCode.Value, 2)

For Each rgLoop In Sheets("lookupModule").[a1].CurrentRegion.Offset(1).SpecialCells(xlCellTypeVisible).Columns(1).Cells
    If Len(rgLoop) > 0 Then
        Me.cbo_moduleCode.AddItem rgLoop & " - " & rgLoop.Offset(, 1)
    End If
Next rgLoop

End Sub

[BobRodes]

First off, you should be able to use cbo_moduleCode.Clear to empty your combo box as you would in VB6.

Now I think you are saying that when the user clicks on a row in a combo box populated with the data in lookupDept the other combo box in your form should be updated to contain only those items in lookupModule. To this end, you apply a filter to the lookupModule data each time the user makes a combobox selection and repopulate your combobox. Is that correct?

And then you said those terrible words "doesn't work". which only tell me that you aren't getting the result you expected, and nothing about the result you're getting. So now I'm all confused again, and it's your fault.