Populate Combo-box from Multiple Sheets
 

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?

Populate Combo-box from Multiple Sheets
 

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

' The items are in O2:O10000
for each sh in worksheets(Array("Sheet2","Sheet5","Sheet7"))
Set GainCells = sh.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

Next sh

' 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


--
Regards,
Tom Ogilvy

"MAWII" wrote:

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?

Populate Combo-box from Multiple Sheets
 

Thanks, Tom.

Is it possible to do it for different ranges? For example, the information
in the first sheet may be in column O, while the same information in the
second sheet may be in column J. I'd prefer not to have to reorganize the
worksheets if possible, but will if I have to.

Thanks!

"Tom Ogilvy" wrote:

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

' The items are in O2:O10000
for each sh in worksheets(Array("Sheet2","Sheet5","Sheet7"))
Set GainCells = sh.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

Next sh

' 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


--
Regards,
Tom Ogilvy

"MAWII" wrote:

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?

Populate Combo-box from Multiple Sheets
 

Certainly. You would have to tell it by sheet what range to look in. Just
use another array to correspond to sheet name.

--
Regards,
Tom Ogilvy


"MAWII" wrote:

Thanks, Tom.

Is it possible to do it for different ranges? For example, the information
in the first sheet may be in column O, while the same information in the
second sheet may be in column J. I'd prefer not to have to reorganize the
worksheets if possible, but will if I have to.

Thanks!

"Tom Ogilvy" wrote:

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

' The items are in O2:O10000
for each sh in worksheets(Array("Sheet2","Sheet5","Sheet7"))
Set GainCells = sh.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

Next sh

' 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


--
Regards,
Tom Ogilvy

"MAWII" wrote:

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?