passing ComboBox to subroutine

[Anand]

I'm having quite a few combo boxes in the excel gui those I need to
fill in with data in same fashion. Hence I created a subroutine to
fill in the combo box.
My idea is to pass the name of the combo box to a subroutine and then
the subroutine will fill in the data appropriately for the said combo
box.
This I can then repeat for other combo boxes.

However, I'm not yet successful doing so. Any help in how I can
achieve this would be appreciated.

Thanks,
Anand.

 

[Tim Williams]

Why not show what you have so far ?

Tim

 

[Anand]

Hi Anand

I agree with Tim it would be good to see your code so far as it is a
lot easier to figure out how to help when you can see what you are
trying to do. However it sounds to me like you are trying to fill all
the comboboxes on your form one after an other??? The code below is
one way of doing just that, to test the code set up a userform with 4
comboboxes and a button leaving all the names as the default then
paste the code to the userform module. Then fill out some data in the
active sheet, column A will fill combobox1 - column B will fill
combobox2 etc.

I hope this gives you some idea of the method used.

Option Explicit
Dim Ctrl As Control
Dim i As Integer
Dim c As Integer

Private Sub CommandButton1_Click()

For i = 1 To 4 '4 is the number of comboboxes

Set Ctrl = UserForm1.Controls("ComboBox" & i)

For c = 1 To Cells(65536, i).End(xlUp).Row

Ctrl.AddItem (Cells(c, i))

Next c

Next i

Set Ctrl = Nothing

End Sub

I hope this helps

Steve

Hi,

Here is what I'm trying to do:

Public Sub selectID(cboBox) ' cboBox should be the combo box handle
Dim IDClm As Range
Dim LastRow As Long
Dim IDStr As String

Set IDClm = shtIDLists.Range(shtIDLists.Cells(4, 5),
shtIDLists.Cells(5000, 5))
LastRow = getLastUsedRow(IDClm) ' This is working subroutine
gives last used row
For lrow = 4 To LastRow
If Not IsEmpty(shtIDLists.Cells(lrow, 5)) Then
IDStr = shtIDLists.Cells(lrow, 5).Value
cboBox.AddItem IDStr
End If
Next lrow
End Sub

I want to call this subroutine from different forms:

Form1 -> selectID(comboBox1);

Form2 -> selectID(comboBox2);

How can I achieve this?

thanks,
Anand.

 

[Tim Williams]

From within a form you should just be able to do:

selectID Me.ComboBoxName

That doesn't work for you ?

Tim

 

[Anand]

From within a form you should just be able to do:

selectID Me.ComboBoxName

That doesn't work for you ?

Tim

Well, I tried but it does not work. I guess, it is related to passing
the comboBox to the routine. The routine is not able to locate the
comboBox properly.
The routine is located in separate module. This routine is supposed to
work with different forms as well. Do I need to pass the Form handle
as well?

My question is the routine gets the name of the box as a string. How
would it figure out where the combo box is located and what is the
actual name(handle) to the comboBox?

thanks,
Anand.

 

[Tim Williams]

Don't pass the combo as a string - pass an object reference.

Public Sub selectID(cboBox as object)

Tim

 

[Anand]

Don't pass the combo as a string - pass an object reference.

Public Sub selectID(cboBox as object)

Tim

It worked !!! Thanks a lot!!!!