Column Colors
By Richard Rost
4 years ago
Changing Column Colors in Access List Boxes
In this Microsoft Access tutorial, I will teach you how to change the colors of individual columns in your list boxes and combo boxes.
Links
Available Colors
- Black, Blue, Green, Cyan, Red, Magenta, Yellow, White
- See Microsoft's Site for details
 Keywords
access 2016, access 2019, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, list box change colors, ListBox.ForeColor property, List Box with Format and Color, Back color and fore color for list box, MS Access VBA Listbox with row color
Subscribe to Column Colors
Get notifications when this page is updated
Intro In this video, I will show you how to change the foreground color of different columns in a list box or combo box in Microsoft Access. We will talk about using the Format property in a query to apply color formatting, work through text and number fields, and see how to handle converting numbers to strings so their colors display properly. You'll also learn the limitations of color formatting in list boxes and combo boxes, and see a quick trick to improve the appearance of your Access forms.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I'm going to show you how to change the foreground color of different columns in a list box or combo box.
Lots of different people have asked me if you can change the colors in a list box or a combo box, but generally you can't. If you make a list box, let's say we make a list box right here with our customers in it, we'll grab a list box from there and drop it down here. I'll pick the values from a table or query, go to the customer table, and bring in customer ID, first name, and last name. Next, sort it, next, and then finish.
Leave that label. Here's my list box. It looks okay, but there's really not much you can do to change the format inside of that list box. If you go to design view and take a look at it, you know you can change the color of the whole thing. You can make it a light blue background, but you can't really change the text in there. Or can you?
You could change the widths in here, and you could change a bunch of other stuff, but there are no properties in the list box itself for changing the colors of the different columns. So you have to remember this little trick. Let's delete this guy. Close the main menu for now, we'll come back to it.
Now, you could put this right inside the table, but I prefer doing it in a query, because if you change the properties in the table then everybody based on that table is going to inherit those changes. Let's make a query with our colors in it, and then we'll use this colored query wherever we want to see these colors.
Go to Create, Query Design. We're going to bring in our customer table. Let me close that. Bring in customer ID, first name, and last name. Now we're not going to see customer ID anywhere, so we're just going to worry about the first name and last name.
Bring up the property sheet over here. If you don't see it, you can just right-click anywhere and go to Properties. There it is. Now, each column, each field, has a Format property. I covered this Format property in detail in my full classes, starting with Access Beginner 3. I'll put a link to that down below if you want to go check it out.
You can put all kinds of different format codes in here for how you want the data to display, how many characters. For numbers you can control the decimal places, all that stuff. But for today, I'm just going to teach you how to do the color trick.
So in the Format property for first name, I want you to put inside a square bracket the word red and then an at-sign like that. I'll zoom in so you can see it better. Just like that: red and then an at-sign. Hit OK.
Now run the query and look at that, first name is now red. Isn't that cute? Let's do last name: click on Last Name, go to Format, and then put the word blue and then an at-sign. Got that at-sign? Now run it and now it's blue. Isn't that cute?
One more, let's add a number field. Now, number fields are tricky. Let's bring in Family Size. They work a little differently, and the number signs for some reason, the color won't come over to the list box. I'll show you a trick to get around that.
So for the Format over here, let's put the word magenta and then a zero. You have to use a zero for numbers. Look at that! With the full format codes, you can control negative numbers, positive numbers, zeros, etc. I cover all that in my full courses.
Now let's save this guy as Customer Color Q, our customer table with the colors in it. Now let's go make a list box out of that.
Go back to the main menu, right click, design view, find a list box, and drop it on here. Get the values from a table or query. We're going to get them from queries, Customer Color Q. Next, bring over all the fields, next, next. That's what it's going to look like. We'll hide the customer ID, next, and then finish.
Let's see what we got. Ready? Boom! There we go. We got a list box with colors. But look, the magenta didn't come through, because for some reason the folks at Microsoft didn't propagate that color for number type fields, which is really weird.
So we just have to convert that to a text value in the query. I know it's an extra step. You have to do it, but I wanted to show you why before I did it in the query.
Let's delete that again and start over. Go back to that query, design view. Now, Family Size is a number. We have to change it over to a text string. So how do we do that? We use the Convert to String function. We'll call this Fam Size now, and it's going to be CSTR (convert to string) and then put Family Size inside parentheses like that. Zoom in so you can see it better.
So now it's called Fam Size. Convert to string, Family Size, that's going to make it into a string value. Then we just change the format up here, get rid of the zero and put a text format in there.
Now run it. It looks the same, but now it's a text value.
Watch this. Close it, save it, back to the main menu, design view, drop in that list box. Here we go. Same thing: Query, Customer Color, next, bring them all over, next, sort it if you want, next, hide that customer ID field, resize these if you want, next, and then finish. Get rid of that label and 1, 2, 3, there's your color.
Pretty cool, isn't it? Now, yes, you can only change the colors, one solid color. That's all you get. You can change the background color using the list box properties. You cannot change the individual rows. You can do that with conditional formatting in a continuous form. I've got another video where I show you how to do that, create a continuous form and with conditional format, and you can change the different rows in there, but you can't do it straight with a combo box or a list box.
But there you go. There's your formatted list box with some color in it. Looks better than nothing. If you want to learn more about the Format property, I cover it in more detail in my Access Beginner 3 class. I'll put a link to that down below. You can check it out if you want to and I hope you learned something today. We'll see you next time.
How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free Expert class each month after you finish the beginner series.
Platinum members get all the previous perks plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free Developer class each month after you finish the expert classes. These are the full-length courses found on my website, not just for Access, too. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond sponsor and have your name or company name listed on a sponsors page. You'll be shown in each video as long as you're a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website.
But don't worry, these free TechHelp videos are going to keep coming as long as you keep watching them. I'll keep making more and they'll always be free.Quiz Q1. What is the main limitation of formatting a list box or combo box in Microsoft Access by default?
A. You can only change the text size.
B. You can only change the foreground color of specific columns.
C. You can only change the overall background color, not individual columns.
D. You cannot change any formatting at all.
Q2. Where does Richard recommend applying color formatting for fields, rather than directly in the table?
A. In the form design
B. In a query
C. In the report
D. In VBA code
Q3. Which property must be modified to change the foreground color of a field in a query?
A. Row Source property
B. Control Source property
C. Format property
D. Default Value property
Q4. To display text in red using the Format property for a text field, which format code should you use?
A. [blue]@
B. red@
C. [red]@
D. magenta@
Q5. What happens when you format a number field with a color in a query and then display it in a list box?
A. The color displays as expected
B. The number disappears
C. The color does NOT propagate into the list box
D. The background color changes instead
Q6. How can you work around the number field color formatting limitation in a list box?
A. Change the background color of the form
B. Convert the number field to a string using the CSTR function
C. Use VBA code to set the color
D. Use a continuous form instead
Q7. What is a limitation of coloring columns in list boxes or combo boxes in Access?
A. You can only set different colors for each row
B. You can only set background colors per column
C. You can only set one solid foreground color per column
D. You can use conditional formatting for each cell
Q8. If you want to have individual row colors based on conditions, what should you use instead of a list box?
A. Subform in datasheet view
B. Continuous form with conditional formatting
C. Split form
D. Pivot Table
Q9. Which membership level gives you access to all the downloadable sample databases and code vault?
A. Silver
B. Gold
C. Platinum
D. Diamond
Q10. Which function is used to convert a number field to a text field for formatting purposes?
A. CNUM
B. CSTR
C. CVAR
D. CTXT
Answers: 1-C; 2-B; 3-C; 4-C; 5-C; 6-B; 7-C; 8-B; 9-B; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone focuses on how to modify the foreground color of different columns in a list box or combo box in Microsoft Access.
This is a question I get often: can the colors in a list box or combo box be customized? The short answer is that, by default, Access does not provide built-in options to control individual column colors in these controls. For example, if you add a list box to a form and set it up to display customer information (such as customer ID, first name, and last name), you will find that the formatting options are quite limited. While you can adjust the background color of the entire list box, Access offers no direct method to change the text color for individual columns.
However, there is a trick you can use by leveraging the Format property within queries. Rather than applying formatting changes at the table level, which would affect all forms and reports based on that table, it is much more flexible to work with queries. This allows you to specify formatting only in the places you need it.
To get started, you can create a query based on your Customer table, bringing in the fields you want to display, such as customer ID, first name, and last name. Now, each field in a query has a Format property. This Format property controls how the data appears, including options for colors using simple formatting codes.
For example, to make the first names in your query appear red, you can enter the format code [Red]@ in the Format property for the First Name field. When you run the query, you will see that the first names are now displayed in red. Similarly, if you set the Last Name field's Format property to [Blue]@, those values will appear in blue.
If you want to include a numeric field, such as Family Size, things work a bit differently. You can set its Format property to [Magenta]0 for numbers, but when that query is used as the source for a list box, you may notice that the color formatting for number fields does not carry over. That is due to an Access limitation where number formatting does not propagate into list boxes in the same way as text fields.
The workaround for this is to convert your number field into a text field within the query. By using the CStr function, you can turn Family Size into a string, and then apply a text-formatting code. With this change, the color formatting will be displayed correctly in your list box.
After saving and closing your query, you can set up a new list box in your form and base it on this query. Now, when you view the list box, you will see that each column is displayed in the color you specified in the query's Format property, even for the column that previously displayed as plain text.
There are some limitations to this method. You can assign only a single, solid color to each column, not to individual rows. To achieve row-level formatting, you would need to use conditional formatting in a continuous form, which I demonstrate in another video. However, for simple multi-color columns in list or combo boxes, this query-based formatting approach works well.
If you want to learn more about the Format property and how you can use it with Access queries, I discuss it in more detail in my Access Beginner 3 class, which is linked below.
I also want to mention that my website offers various membership levels, each with different perks. Silver members and above can access extended cut TechHelp videos and a free beginner class each month. Gold members also receive the ability to download all sample databases from my TechHelp videos, access to my code vault, and higher priority for submitting questions, plus one free Expert class per month after finishing the beginner series. Platinum members receive all previous benefits, even higher priority support, access to all full beginner courses (not just for Access, but also Word, Excel, Visual Basic, and more), and one free Developer class per month after completing the Expert courses.
Don't forget, all the TechHelp videos are free and will continue to be available. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Creating a list box with multiple columns from a table or query
Adjusting list box properties in design view
Limitations of list box column formatting in Access
Using the Format property for fields in a query
Applying color formatting to text fields using the Format property
Formatting number fields for color display in queries
Converting number fields to text using CStr for formatting
Formatting string-converted number fields with color
Building a query to drive a colored list box
Creating a list box from a color-formatted query
Troubleshooting why color formatting fails for number fields
Assigning specific colors to different columns in a list box
Understanding limitations of list/combo box formatting vs continuous forms |
