Cell Dependant Format using combo box

LearningExcelAgain

New Member
Joined
May 25, 2018
Messages
2
I will start by saying that I have successfully gotten a couple of VBA macros to work but I do not know what I am doing...

I have added some combo boxes into a spread sheet. These boxes reference cells in a column that is defined. I can get the selection to work ie. I select Good from the list and it brings it into the list box. The issue that I am having is that I want the text color to follow as well. The words in the list are formatted as follows:
Good (Green)
Bad (Orange)
Ugly (Red)
When I select any of the words it comes across as just black text without the underlying color. Also since the individual selections have a different preferred color setting the format of the box will not do what I want.

any ideas on this?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
I'm a little confused.

You first said:
I have added some combo boxes into a spread sheet
I select Good from the list and it brings it into the list box

So your saying when you choose Good from the Combobox you want the word Good entered into a Listbox
and you want the word Good displayed in the listbox with the font color being Green.

Is that what you wanting?

If that is what your wanting I do not believe that is possible

And it's always best to tell us what type Combobox and what type Listbox

We have Activex Comboboxes and Form Control list boxes and UserForm Comboboxes and listboxes

And it's always best to provide the names of these controls.

Like I have combobox named Combobox1 and Listbox named Listbox8
 
Upvote 0
I also see you said:
I select Good from the list and it brings it into the list box.

But your subject title for your posting says:
Cell Dependant Format using combo box

So are you saying if you select Green you want the text entered into a cell and you want the font color in the cell to be green?

The problem is you said:
I select Good from the list and it brings it into the
list box
you did not say
cell

 
Upvote 0
Clarification of the questions you asked.

I am using an ActiveX combo box control. I have it linked to a list in the spread sheet. The font colors of the individual choices are set to the preferred colors in the cells. What I want to do is have these font colors follow the selection into the combo box display field.

The combo box works as expected. When I drop down the list and select one of the items I do get that item displayed in the combo box field. The intent of the coloration is to draw immediate attention to the choices made (this is going to be printed and customer signed).
 
Upvote 0
Assuming your "Combobox" is on your active sheet and is not filled by the "Listfillrange" property then.

You can fill your combobox by the first code below.
This assumes your data is in column "A" , Alter range address in code to suit.

This will fill the combobox with your data , and will fill a second column with the colour of the individual cells.
Code:
[COLOR="Navy"]Sub[/COLOR] MG30May23
[COLOR="Navy"]Dim[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
ComboBox1.Clear
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Range("A1:A10")
    [COLOR="Navy"]With[/COLOR] ActiveSheet.ComboBox1
        .AddItem R.Value
        .List(.ListCount - 1, 1) = R.Interior.Color
     [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Using the code below for your Combobox Change event will change the Combobox.Backcolor to the appropriate colour,
based on the colour shown in column 2 of the combobox.

Rich (BB code):
Private Sub ComboBox1_Change()
With ComboBox1
    .BackColor = .List(.ListIndex, 1)
End With
End Sub

Regards Mick
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top