Trouble using variables across different subs

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

Hi,

maybe something like this?

Please Login or Register  to view this content.

I didn't check the rest of the code but this should be the syntax for calling other procedures that have arguments.

Thanks RHCPergo!

That helped me get to the last problem (I think). The variable "Cboxii" is not recognized in Load1Cbox.

Solved it Norie!... Thanks very much!