I'm trying to populate several ComboBox's each time a filter is applied. I'm pretty new to VBA and am having difficulty with variables and calling macro's which use variables.

Any help is much appreciated (I've pasted my code below)

Thanks!



Public colii As String, cboxii As String
'***************************************************************
'***** This Sub removes duplicate items and loads a single ComboBox *****
'***** Obtained on the net & works good *****
'***************************************************************
Public Sub Load1Cbox(ByRef cboxii As String, ByRef colii As String)

'Load a single combobox making sure no items are duplicated

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

FormNavigator.cboxii.Clear ' variable (Cboxii)
Set AllCells = Sheets("IN-WORK").Range(colii) ' variable (Colii)

On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.value, CStr(Cell.value)
Next Cell
On Error GoTo 0

For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

' Add the sorted, non-duplicated items to a ListBox
For Each Item In NoDupes
FormNavigator.cboxii.AddItem Item ' variable (Cboxii)
Next Item
FormNavigator.cboxii.ListIndex = 0 ' variable (Cboxii)
End Sub

'*******************************************************************
'***** This Sub is intended to load several ComboBox's each time it is run *****
'***** Intent is to re-populate from active x menu combobox *****
'***** Does not run - (I'm not familiar with passing variables?? *****
'**************************************************************

Public Sub Load3Cboxs()
Load1Cbox , colii = "A5:A500", cboxii = "ComboBox1" 'Tool Order Search Box
Load1Cbox , colii = "B5:B500", cboxii = "ComboBox6" 'Tool Number Search Box
Load1Cbox , colii = "C5:C500", cboxii = "ComboBox8" 'Customer Filter (COE)
Load1Cbox , colii = "D5:D500", cboxii = "ComboBox9" 'Department Filter
End Sub