Passing an object as a parameter to a sub?

[Ian]

Using Word 97.

Before allowing a user to close a form, I want to check that none of the
boxes on the form are empty, using syntax something like:

~~~~~~~~~~~~~~~~
With MyForm
If .MyComboBox1.Value = "" then
MsgBox "Error: Box 1 is undefined"
End With
~~~~~~~~~~~~~~~~

This is fine for one or two boxes, but for many boxes it would be better
to create a subroutine to do the job, and pass the name of the box (e.g.
".MyComboBox1") and the corresponding variable error text (e.g. "Box 1")
as parameters to it.

Question is: How do I pass ".MyComboBox1" as a parameter to a
subroutine, so that I can dynamically build the object
".MyComboBox1.Value"?

 

[Dave Lett]

Hi Ian

You can pass the parameter as a string (if you've named all of your controls). Something like the following might work

'''--------------------------------
Private Sub cmdNew_Click(
MsgBox fControlBlank("cmbNames"
frmTest.Hid
Unload frmTes
End Su
'''--------------------------------
'''--------------------------------
Public Function fControlBlank(sControlName As String) As Boolea
fControlBlank = Fals
If frmTest.Controls(sControlName) = "" The
fControlBlank = Tru
End I
End Functio
'''--------------------------------

However, this doesn't cycle through the controls, it passes only one name. You can make the clicking routine cycle through an array of names and test each one. I think it all gets a bit cumbersome, especially if you ever to deicde to rename/label one of your controls. I'd suggest that you cycle through the _all_ the controls and set up conditionals from there, as in the following

'''--------------------------------
Private Sub cmdNew_Click(
fControlBlan
frmTest.Hid
Unload frmTes
End Su
'''--------------------------------
'''--------------------------------
Public Sub fControlBlank(
Dim iCtl As Intege
Dim sControlNames As Strin
sControlNames = "
For iCtl = 0 To Me.Controls.Count -
If TypeOf Me(iCtl) Is TextBox Or
TypeOf Me(iCtl) Is ComboBox The
If Me.Controls(iCtl).Value = "" The
sControlNames = sControlNames & Me.Controls(iCtl).Name & vbCrL
End I
End I
Next iCt
MsgBox sControlName
End Su
'''--------------------------------

HTH
Dave

 

[Perry]

Hi Ian,

So you want to make use of one routine to check whether
a userform with (for instance) a combobox is empty, right?

I'll give you two examples:
One in which a particular combobox is passed as parameter; the other
includes a userform as parameter to a validating function, here goes:

'>> functions in a standard module
' this is the one for a particular combobox
Function IsEmpty_Combo(ByVal MyCombo As MSForms.ComboBox) _
As Boolean

If MyCombo = "" Then
IsEmpty_Combo = True
MsgBox MyCombo.Name & " is empty"
End If
End Function

'the other one, in which a userform is passed as parameter
Function IsEmpty_a_Combo(ByVal MyForm As Object) _
As Boolean

Dim c As Control

For Each c In MyForm.Controls
If TypeOf c Is ComboBox And c = "" Then
IsEmpty_a_Combo = True
MsgBox "At least one empty combobox: " & c.Name
Exit Function
End If
Next
End Function

Using above functions in (for instance: UserForm_QueryClose) in UserForm
codemodule:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If IsEmpty_a_Combo(Me) Then
Cancel = True
End If
' or use the other function
If IsEmpty_Combo(Me.ComboBox1) Then
Cancel = True
End If
End Sub

Krgrds,
Perry

 

[Ian]

Perry.

The second example was exactly what I was looking for. Many thanks.

Ian