How to Pass a Form to a Function - 2007

[Aaron]

Hi,
Is there a way to pass a form to a function in VBA. For example, I have
created several userforms all of which have a combobox that I want filled
using the same set of code. Instead of including this code in each of the
forms code, I want to make this a function in a module and have the form just
call the function to fill the combobox. The function would accept the form
name or whatever and use it to fill the combobox.
I am new to VBA and have tried using
Public Function FillSize(Obj As MSForms.UserForm)
For i = 1 To Obj.cmdSize.ListCount
'Removes an item from the Combo Box
Obj.cmdSize.RemoveItem 0
Next i

With Obj.cmdSize
.AddItem "AddStuff" 'Add to combo box by reading excel sheet

End With
End Function

And I would call the function using
FillSize(Me)
OR
FillSize(UserForm_Name)

Unfortunately I am not the most experienced of programmers and I am unsure
how to accomplish this, if my code is even a step in the right direction, or
even if it possible to accomplish what Im asking.

Thanks for any help in advance,

 

[Ed Sowell]

Aaron,
Since it's a Public function it can be called from the code for any form.

Also, you can refer to the controls of any form anywhere by using the form
name. E.g.,

frmMyForm.cboList

Ed

 

[Aaron]

Thanks for the Reply Ed,

Now when I implement the code, I have this kind of thing
Public Function FillSize(Obj)
For i = 1 To Obj.cmbSize.ListCount
'Removes an item from the Combo Box
Obj.cmbSize.RemoveItem 0
Next i>
With Obj.cmbSize
.AddItem "AddStuff" 'Add to combo box by reading excel sheet>
End With
End Function
And I send the form to the function using
FillSize(frmName)

However, on the line (and Im sure on the lines afterwards)
'For i = 1 To Obj.cmbSize.ListCount'

I get "RunTime error 438, Object doesnt support this property or method."
I'm not sure how to fix this problem and I am not sure which datatype 'Obj'
should be. I also get the same error when I set Obj As Object. Any code
examples or anything where something similar is done would help me a lot as I
cant seem to find any anywhere.

Any input is greatly appreciated. Thanks.

 

[Jon Peltier]

I think I'd try passing the form as a plain old object:

Public Function FillSize(Obj As Object)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______

Thanks for the Reply Ed,

Now when I implement the code, I have this kind of thing
Public Function FillSize(Obj)
For i = 1 To Obj.cmbSize.ListCount
'Removes an item from the Combo Box
Obj.cmbSize.RemoveItem 0
Next i>
With Obj.cmbSize
.AddItem "AddStuff" 'Add to combo box by reading excel
sheet>
End With
End Function
And I send the form to the function using
FillSize(frmName)

However, on the line (and Im sure on the lines afterwards)
'For i = 1 To Obj.cmbSize.ListCount'

I get "RunTime error 438, Object doesnt support this property or method."
I'm not sure how to fix this problem and I am not sure which datatype
'Obj'
should be. I also get the same error when I set Obj As Object. Any code
examples or anything where something similar is done would help me a lot
as I
cant seem to find any anywhere.

Any input is greatly appreciated. Thanks.

 

[Ed Sowell]

Aaron,

I've not tried passing a form as an argument. Never felt the need to. They
way I do things is to have forms that have dotheform code, with helper
functions when justified either because of their complexity or simply too
many lines of code. Another reason would be if the same function is needed
by other forms.

If a form gets too complex from the users point of view I will sometimes use
a subform that the user can lanuch by the click of a button. In the
dotheform code for the main form I load the subform, launch it, then hide it
upon return. If there is a need, I customize the subform by setting some of
the controls, labels, etc. after loading it in the main form. With this
structure results of the subform, e.g., data obtained from the user, is
"returned" to the main form by writing into the control fields of the main
form from the dotheform code in the subform. This seems to be powerful
enough for my needs, and I never have to pass forms as arguments.

Ed

Thanks for the Reply Ed,

Now when I implement the code, I have this kind of thing
Public Function FillSize(Obj)
For i = 1 To Obj.cmbSize.ListCount
'Removes an item from the Combo Box
Obj.cmbSize.RemoveItem 0
Next i>
With Obj.cmbSize
.AddItem "AddStuff" 'Add to combo box by reading excel
sheet>
End With
End Function
And I send the form to the function using
FillSize(frmName)

However, on the line (and Im sure on the lines afterwards)
'For i = 1 To Obj.cmbSize.ListCount'

I get "RunTime error 438, Object doesnt support this property or method."
I'm not sure how to fix this problem and I am not sure which datatype
'Obj'
should be. I also get the same error when I set Obj As Object. Any code
examples or anything where something similar is done would help me a lot
as I
cant seem to find any anywhere.

Any input is greatly appreciated. Thanks.