Hi all,
I have an excel sheet in the form of my attachment. The first column states the department (cbosubdivision), the second the job level (cbojoblevel) that could be on the same department more than once, and the third some kind of description (cbojobdescription). I have three comboboxes of which the first one shows on a drop down list unique data e.g. Department A, Department B. The second shows depending on the Department chosen, the various choices but only again in a unique drop down list e.g. A,B,C,D. The third combobox i would like it to show the corresponding choices for each of the previous choice, meaning if selected Department A and A, then the choices abc and sdr will be shown on the list of the third combobox to choose. I already found some scripts and applied them appropriately until the second dropbox, but on the third i cannot get it working..
Any help appreciated.
Private Sub cboSubDivision_Click()
Call FillJobLevelComboBox(GetActiveSheet, cboJobLevel, cboSubDivision.List(cboSubDivision.ListIndex))
End Sub
Private Sub cboJobLevel_Click()
Call FillJobDescriptionComboBox(GetActiveSheet, cbojobdescription, cboSubDivision.List(cboSubDivision.ListIndex), cboJobLevel.List(cboJobLevel.ListCount - 1, 1))
End Sub
Private Sub cbojobdescription_Change()
End Sub
Sub FillSubDivisionComboBox(strSheetName As String, objComboBox As Object, strDivision As String)
Dim lngRows As Long
Dim lngctr As Long
'First, Cleanup the combobox
objComboBox.Clear
With Worksheets(strSheetName)
lngRows = .Cells(.Rows.count, 2).End(xlUp).Row
For lngctr = GetStartRow(strSheetName, "Column 1", "A:A") To lngRows
If .Range("A" & lngctr) = strDivision Then
If IsValueExistInComboBox(objComboBox, .Range("B" & lngctr)) = False Then
objComboBox.AddItem .Range("B" & lngctr)
End If
End If
Next lngctr
End With
End Sub
'Same procedure for job level position box. Define double columns on this combobox
Sub FillJobLevelComboBox(strSheetName As String, objComboBox As Object, strDivision As String)
Dim lngRows As Long
Dim lngctr As Long
objComboBox.Clear
'2 columns in this combobox, one for Row Number and the other for the value
objComboBox.ColumnCount = 2
'Column Width. Row number width = 0 (we don't want to show this) and value width = 30
objComboBox.ColumnWidths = "0;30"
With Worksheets(strSheetName)
lngRows = .Cells(.Rows.count, 3).End(xlUp).Row
For lngctr = GetStartRow(strSheetName, "Column 1", "A:A") To lngRows
If .Range("A" & lngctr) = strDivision Then
If IsValueExistInComboBox2(objComboBox, .Range("B" & lngctr)) = False Then
objComboBox.AddItem lngctr 'Row number
objComboBox.List(objComboBox.ListCount - 1, 1) = .Range("B" & lngctr)
Else
End If
End If
Next lngctr
End With
End Sub
Sub FillJobDescriptionComboBox(strSheetName As String, objComboBox As Object, strDivision As String, strjoblevel As String)
Dim lngRows As Long
Dim lngctr As Long
'First, Cleanup the combobox
objComboBox.Clear
With Worksheets(strSheetName)
lngRows = .Cells(.Rows.count, 3).End(xlUp).Row
'Loop thru the end.
For lngctr = GetStartRow(strSheetName, "Column 1", "A:A") To lngRows
If .Range("A" & lngctr) = strDivision Then
If .Range("B" & lngctr) = strjoblevel Then
' If IsValueExistInComboBox2(objComboBox, .Range("C" & lngCtr)) = False Then
objComboBox.AddItem .Range("C" & lngctr) 'Row number
' End If
End If
End If
Next lngctr
End With
End Sub
Bookmarks