I have a userform with combo-boxes and I'm trying to populate them from
information on multiple worksheets. The code I currently have to fill them
from one sheet is as follows ("Gain" is just to specify which box, since
there are many):

Dim GainCells As Range, CellGain As Range
Dim NoDupesGain As New Collection
Dim iGain As Integer, jGain As Integer
Dim Swap1Gain, Swap2Gain, ItemGain

' The items are in O2:O10000
Set GainCells = Range("O2:O10000")

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each CellGain In GainCells
NoDupesGain.Add CellGain.Value, CStr(CellGain.Value)
' Note: the 2nd argument (key) for the Add method must be a string
Next CellGain

' Resume normal error handling
On Error GoTo 0

' Sort the collection (optional)
For iGain = 1 To NoDupesGain.Count - 1
For jGain = iGain + 1 To NoDupesGain.Count
If NoDupesGain(iGain) NoDupesGain(jGain) Then
Swap1Gain = NoDupesGain(iGain)
Swap2Gain = NoDupesGain(jGain)
NoDupesGain.Add Swap1Gain, befo=jGain
NoDupesGain.Add Swap2Gain, befo=iGain
NoDupesGain.Remove iGain + 1
NoDupesGain.Remove jGain + 1
End If
Next jGain
Next iGain

' Add the sorted, non-duplicated items to a ListBox
For Each ItemGain In NoDupesGain
Antenna_Gain.AddItem ItemGain
Next ItemGain

How do I adjust this to add a range from another sheet?