Private Sub ComboBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'-----------------------------------------------------------------------------------
'Adapted: http://www.excelforum.com/excel-programming/661182
'-update-a-combo-box-list-which-list-is-on-a-different-worksheet.html
'-----------------------------------------------------------------------------------
'If new sector is entered, add to combobox drop-down list
If WorksheetFunction.CountIf(Range("test"), ComboBox1.Value) = 0 Then
With Range("test")
If .Rows.Count = 1 Then
.Offset(.Row) = ComboBox1.Value
Else
.End(xlDown).Offset(1) = ComboBox1.Value
End If
.Sort .Cells(1)
ComboBox1.RowSource = .Parent.Name & "!" & .Address
End With
End If
End Sub
Bookmarks