ExcelBanter - Selectively Populate Excel VBA Combo Box

Hi,
I currently have a combo box which is populated by data from an Excel
spreadsheet. I wish to have the combo box only be populated with data
which is not on another spreadsheet (within the same workbook).
For instance, List1 on Sheet1 populates the combo box - And then List 2
on Sheet2 contains entries made by my Excel form, drawn from the data
listing on Sheet1. I wish to avoid duplicate entries in part by
removing the already-entered data from the combo box.

Thanks for any help you can provide. :)
Louis

code below
======

'////Background code for combo box population
'////No Editing
Private Function UniqueItemList(InputRange As Range, _
HorizontalList As Boolean) As Variant
Dim cl As Range, cUnique As New Collection, i As Long, uList() As
Variant
Application.Volatile
On Error Resume Next

For Each cl In InputRange
If cl.Formula < "" Then
cUnique.Add cl.Value, CStr(cl.Value)
End If
Next cl
UniqueItemList = ""
If cUnique.Count 0 Then
ReDim uList(1 To cUnique.Count)
For i = 1 To cUnique.Count
uList(i) = cUnique(i)
Next i
UniqueItemList = uList
If Not HorizontalList Then
UniqueItemList = _
Application.WorksheetFunction.Transpose(UniqueItem List)
End If
End If

On Error GoTo 0
End Function

Private Sub cboCourse_Change()
'////Contains editable settings
'////////////
'Check if ListIndex = 0 (first option in combo box)
With Me.cboCourse2
'.Clear
If Me.cboCourse.ListIndex = 0 Then
Label4.Visible = False
Me.cboCourse2.Visible = False
..Clear ' Clear the list box content
End If
End With
'////////////

'////////////
'Check if ListIndex = 1 or "all" (second option in combo box)
With Me.cboCourse2
If Me.cboCourse.ListIndex = 1 Then
Me.Label4.Visible = True
Me.cboCourse2.Visible = True
..Clear ' Clear the list box content

'////Edit range below
'the variable below is to populate the combo box
MyUniqueList2 = UniqueItemList(Sheet1.Range("A1:A385"), True)
'loop below for combo box
For i = 1 To UBound(MyUniqueList2)
..AddItem MyUniqueList2(i)
Next i

'loop below for accurate row count
Me.cboCourse2.ListIndex = 0 'select the first item

End If
End With
'////////////

'////////////
'Check if ListIndex = 2 or "A-M Books" (third option in combo box)
With Me.cboCourse2
'.Clear
If Me.cboCourse.ListIndex = 2 Then
Me.Label4.Visible = True
Me.cboCourse2.Visible = True
..Clear ' Clear the list box content

'////Edit range below
'the variable below is to populate the combo box
MyUniqueList3 = UniqueItemList(Sheet2.Range("B1:B10"), True)

'loop below for combo box
For i = 1 To UBound(MyUniqueList3)
..AddItem MyUniqueList3(i)
Next i
'i = 0

'loop below for accurate row count

Me.cboCourse2.ListIndex = 0 'select the first item

End If
End With
'////////////

'////////////
'Check if ListIndex = 3 or "N-Z Books" (third option in combo box)
With Me.cboCourse2
'.Clear
If Me.cboCourse.ListIndex = 3 Then
Me.Label4.Visible = True
Me.cboCourse2.Visible = True
..Clear ' Clear the list box content

'////Edit range below
'the variable below is to populate the combo box
MyUniqueList4 = UniqueItemList(Sheet2.Range("B11:B80"), True)

'loop below for combo box
For i = 1 To UBound(MyUniqueList4)
..AddItem MyUniqueList4(i)
Next i
'i = 0

'loop below for accurate row count

Me.cboCourse2.ListIndex = 0 'select the first item

End If
End With
'////////////

End Sub