I have a user form with many pull downs using VBA in Excel. The color of the
result box changes color depending on the value. No problem there; however,
not only is the result box changing color so is the pull down with the other
values. Is there a way to change the result box and not change the pull down
color? My boss wants the pulldown to remain white so the user can see the
other possible values.

I thought about using events on click but that's not an option for pull downs.

TIA
Steve

Hello Steve,

If we had access to window handles in VBA this would be easy. This cod
is a work around to your problem. You specify the backcolor of eac
ComboBox (aka Pull Down or Drop Down) in the UserForm's intializ
event. When the user clicks the drop down arrow, the backcolor propert
is set to white. When clicked a second time or when the focus is lost
the backcolor is restored to your original color. All the color value
are RGB values.

You need to add a line of code to each ComboBox's DropDownClick even
to call the backcolor macro. This was easiest code method I coul
devise. The code is in 2 parts: First the UserForm event codes, an
secod the macro module. I use only 2 ComboBoxes in this example, bu
you can you use as many as you need.

USERFORM EVENT COD
_____________________________


Code
-------------------
Private Sub ComboBox1_DropButtonClick()

Call ChangeBackColor(ComboBox1)

End Sub

Private Sub ComboBox2_DropButtonClick()

Call ChangeBackColor(ComboBox2)

End Sub

Private Sub UserForm_Initialize()

On Error Resume Next
With ComboList
.Add vbBlue, ComboBox1.Name
.Add vbYellow, ComboBox2.Name
End With
If Err.Number = 457 Then Err.Clear

End Sub
_____________________________

MACRO MODULE COD
_____________________________

Public ShowList As Boolean
Public ComboList As New Collection

Public Sub ChangeBackColor(ByRef CB As MSForms.ComboBox)

ID = CB.Name

With CB
If Not ShowList Then
.BackColor = vbWhite
Else
.BackColor = Val(ComboList(ID))
End If
End With

ShowList = Not ShowList

End Sub

-------------------

_____________________________

Happy Holidays,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=49593

Leith,
Thanks for the quick reply, works great.
Steve

"Leith Ross" wrote:


Hello Steve,

If we had access to window handles in VBA this would be easy. This code
is a work around to your problem. You specify the backcolor of each
ComboBox (aka Pull Down or Drop Down) in the UserForm's intialize
event. When the user clicks the drop down arrow, the backcolor property
is set to white. When clicked a second time or when the focus is lost,
the backcolor is restored to your original color. All the color values
are RGB values.

You need to add a line of code to each ComboBox's DropDownClick event
to call the backcolor macro. This was easiest code method I could
devise. The code is in 2 parts: First the UserForm event codes, and
secod the macro module. I use only 2 ComboBoxes in this example, but
you can you use as many as you need.

USERFORM EVENT CODE
_____________________________


Code:
--------------------
Private Sub ComboBox1_DropButtonClick()

Call ChangeBackColor(ComboBox1)

End Sub

Private Sub ComboBox2_DropButtonClick()

Call ChangeBackColor(ComboBox2)

End Sub

Private Sub UserForm_Initialize()

On Error Resume Next
With ComboList
.Add vbBlue, ComboBox1.Name
.Add vbYellow, ComboBox2.Name
End With
If Err.Number = 457 Then Err.Clear

End Sub
_____________________________

MACRO MODULE CODE
_____________________________

Public ShowList As Boolean
Public ComboList As New Collection

Public Sub ChangeBackColor(ByRef CB As MSForms.ComboBox)

ID = CB.Name

With CB
If Not ShowList Then
.BackColor = vbWhite
Else
.BackColor = Val(ComboList(ID))
End If
End With

ShowList = Not ShowList

End Sub

--------------------

_____________________________

Happy Holidays,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=495936