Passing ComboBox ActiveX Control as Parameter to Method

[Tony]

I'm using Excel 2000 and VBA. I have the following method that runs
when I change the value of a certain ComboBox ActiveX control:

Private Sub MyComboBox1_Change()

If MyComboBox1.Value = "Please Select" Then
MyComboBox1.BackColor = &H80FFFF
Else
MyComboBox1.BackColor = &HFFFFFF
End If

End Sub

I have about 12 of these methods for 12 different combo boxes. I'd
like to replace them all with something a little simpler:

Private Sub MyComboBox1_Change()

SetColor(MyComboBox1)

End Sub

Private Sub SetColor (cbox As ComboBox)

If MyComboBox1.Value = "Please Select" Then
MyComboBox1.BackColor = &H80FFFF
Else
MyComboBox1.BackColor = &HFFFFFF
End If

End Sub

However, this does not work. I get an error that reads: "Run-time
error '424': Object required."

What am I doing wrong? Also, rather than calling
"SetColor(MyComboBox1)", is there a way to reference the "current"
combo box using a variable similar to "this" in Java?

Thanks for all of your help.

Regards,
Anthony Frasso

[Tony]

Sorry everyone; I copied the subroutine SetColor incorrectly. It
should read:

Private Sub SetColor(cbox As ComboBox)

If cbox.Value = "Please Select" Then
cbox.BackColor = &H80FFFF
Else
cbox.BackColor = &HFFFFFF
End If

End Sub

It just doesn't like the line:

SetColor(MyComboBox1) in the Sub MyComboBox1_Change(). Any clues,
anyone?

Regards,
Anthony Frasso

[Scott Winter]

I forget the exact terms here, however their are two
ways of passing variables/object. One is a copy of the
variable/object and the other is the variable/object
itself. I beleive the default is the copy of the
variable. Also, instead of using a sub, why not use a
function. Something like:

Sub changebackcolor ()
MyCombo.BackColor = ChangeColor(MyCombo.value)
End sub

Function ChangeColor(str as string) as Variant

If str = "Please Select" Then
ChangeColor = &H80FFFF
Else
ChangeColor = &HFFFFFF
End If
End Function

None of this is tested or proven to work, it is only
intended to perhaps get you on a different path to the
solution.

--- Tony <an...@cornell.edu> wrote:

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com