Changing back colour of combo box

[carl6885]

Hi

I am trying to create a module that changes the combo box back colour based on the value.

I have it working using if statements but there is 15 combo box so I just want a module I can reference.

This is what I have so far but it isn't working:

Public Sub ChangeColour(cboControl As ComboBox)
Dim col As Long

Select Case Nz(cboControl.Value, "")
Case "Amber"
col = RGB(237, 135, 45)

Case "Green"
col = RGB(0, 255, 0)
Case Is = "Red"
col = RGB(255, 0, 0)
Case Else
col = RGB(255, 255, 255)
End Select
cboControl.BackColor = col
End Sub

Private Sub cboCorrectGreeting_AfterUpdate()

Me.ChangeColour (Me.cboCorrectGreeting)

End Sub

Can anyone point out where I am going wrong?

Thanks

Carl

 

[mdlueck]

Just a wild guess, receive the control with the added attribute "ByRef" and see if that works better. That way you are dealing with the actual object which was passed to the shared/common code.

I have shared/common code which resets various attributes of Forms. The call simply passes in Me, and to receive it I use:

Sub uiutils_ResetForm(ByRef MePointer As Form)

 

[Galaxiom]

When you place the parentheses around the argument of a sub or function call it is forced to ByVal and the Value property of the combo is passed instead of the object.

Simply remove the parentheses from the call.

 

[carl6885]

Thanks guys - the second option worked a treat.

 

[vbaInet]

Fyi, you could have used Conditional Formatting for this.

 

[carl6885]

Hi

Thanks for your response. I have tried conditional formatting and it worked for 5 minutes and stopped no matter how other DB I tried so found it unreliable.

At least this way I get to practice writing VBA, albeit basic and have reliable code to do the job.

Thanks again.

 

[vbaInet]

When you use Conditional Formatting, I have found that it's best to refer to the Control than the Field and ensure that the name of the control is not the same as the name of its bound field. That was probably why it wasn't working.

Yes, it's good to learn VBA

Good luck with the rest of your project!