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