Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Passing ComboBox ActiveX Control as Parameter to Method

  Asked By: Sam    Date: Mar 22    Category: MS Office    Views: 3880
  

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?

Share: 

 

5 Answers Found

 
Answer #1    Answered By: Adalricus Fischer     Answered On: Mar 22

To start with .... As I read it you should be refering to the
passed combo box in the procedure setcolor instead of trying to look at
MyComboBox1 all the time. SetColour doesn't know the object MyComboBox1 from
Eve!!

To refer to the combo box then can we assume you are in the combo box


In sub SetColour try refering to cbox.BackColor and so on and see if that
helps.

 
Answer #2    Answered By: Ada Bailey     Answered On: Mar 22

I copied the method  SetColor incorrectly!

The actual method is:

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?

 
Answer #3    Answered By: Martha Gonzalez     Answered On: Mar 22

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

Don't you want those "MyComboBox1" references to be "cbox" (the object
parameter passed to the subroutine)?

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

You may be able to use some variation of "Application.Caller"...

 
Answer #4    Answered By: Poppy Brown     Answered On: Mar 22

pls try this
Private Sub SetColor (cbox As ComboBox)

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

 
Answer #5    Answered By: Juanita Mason     Answered On: Mar 22

SetColor(MyComboBox1)

should be

Call SetColor(MyComboBox1)

 
Didn't find what you were looking for? Find more on Passing ComboBox ActiveX Control as Parameter to Method Or get search suggestion and latest updates.




Tagged: