need form pulldown white while box is color

[stindle]

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

[Leith Ross]

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 Activate 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.

Please Login or Register  to view this content.

_____________________________

Happy Holidays,
Leith Ross

[stindle]

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
>
>