|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
List Box with Format and Color? By Richard Rost 
6 years ago
Can you have a List Box with Formatting and Colors in Access? I get asked this question all the time: "can you have colors and other formatting in list boxes in Microsoft Access?" In this video we'll take a look at some options that are available. Simon from Oregon (a Silver Member) asks, "Is it possible to have formatting and colors for different rows in a standard list box?" MembersI'll show you how to prevent users from editing our popup form. You'll also see how to colorize any field in the form based on the value of a different field.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today! LinksContinuous Forms: https://599cd.com/Continuous
IntroIn this video, we will talk about whether you can format or use different colors for rows in a standard list box in Microsoft Access. We'll discuss the limitations of the built-in list box, explore why individual row colors and formatting are not available, and show how to simulate a color-coded list using a continuous form with conditional formatting. I'll also demonstrate how you can select a value from this custom form and update your main form using simple Visual Basic code.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, we are going to answer that age-old question that I get asked all the time. Can you have formatting and colors in a list box? Today's question comes from Simon in Oregon, one of my silver members. Simon asks, is it possible to have formatting and colors for different rows in a standard list box? Well, Simon, the short answer is no. You cannot. The standard Access list box only allows one uniform color. You can have a different color for the selected item, but all of the items in the box have to be of the same color. With that said, when people tell me that they want a colorized or formatted list, they do not necessarily need a list box, and there are some other things we can do to simulate that effect. So let me show you what I mean. One of the questions I get asked a lot is, can I use colors or other formatting in a list box? The answer, unfortunately, is no. There is no way you can do it with the basic Access list box control. For example, let's say I want to be able to pick my state from a list box or a combo box. The same limitation applies in both. You could make a little state list box or a combo box. Let's go up to the control box here and find the list box and then drop it down here. Let's just type in the values that you want. Let's say you have got New York. Let's say you only do business in three states: New York, Florida, and California. Next, and then we are going to store that value in the state field. Next, and then finish. Instead of typing it in here, we have this little list box over here. I am going to save that, close it, and reopen it. All right, notice, Florida is selected. If I change that, see, these are bound to the same control. So I change this one, and this one over here changes. As you move through your records, that is nice because they stay bound. Let's change this to New York. Go back over here, Florida. So that is kind of cool. That is kind of handy. But what if I want to have these colored differently? I want New York to be blue. I want Florida red. I want California green. All right, there is no way in here. If you come in here and change these colors, if I go to Format and change these colors, I am changing the entire list box. Notice, you can get the highlighted one to look different, but there is no way to change the individual ones. You cannot change this via VB code. There is no conditional formatting for it. So, unfortunately, with the built-in list box control, you cannot do that. Now, when I talk to most people who want to do that, what they really want is the ability to colorize a list that they can select from for any different reason, whether it is a sales rep's region, a discount bracket that the customer is in, or whatever reason they want a colorized list. The best way to set up a colorized list is with a continuous form. I have done some videos on continuous forms before. Some of them on YouTube are free, and on my website are free. I know I have covered it in a lot of my classes. I will put some links below the video in the description if you want to learn more about continuous forms. We just want to make a real simple one so we can pick our state from. So let us close this real quick, save changes, sure. Let us go and make another table. Create. Table Design. This will be my list of states. I am going to put in here state name, or state abbreviation, ABBR. We will abbreviate abbreviation. That will be short text. I am going to make that two, only two characters. Again, very limited business. I only do business in three states. I do not foresee that changing in the future, but if it does, I can work with that. Now, I just recorded a very long video on why it is important to use autonumbers, and almost every table should have one, but I did say there are some exceptions. This is going to be a very short list of states. I am going to make this the primary key. It is going to be indexed with no duplicates. New York, Florida, California, that is fine. I am not worried about getting really big with this table. If I do, I can always change it later. You can put a state name in here if you want to, a longer version like New York spelled out. Save this as my state T. Let us put some data in here real quick. New York, New York, like that. Let us go Florida and California. You can have more. Again, for the purposes of class, my company only does business in these three states. Let us create a real simple continuous form so we can select from one of those states. Create. Form Design. The first thing I am going to do is bind this form to a record source, which is a list of states from my state table. I am going to change this to a continuous form. Remember, a single form, you only get one on the screen at a time. Continuous form is a big long list. I am going to shrink this up a little bit. I am going to add existing fields. Let us bring in both of these. We do not need these labels here. Delete. I will put the abbreviation first, that is really short, followed by the state name spelled out afterwards. Shrink that up. It is nice and small. Save it. This will be my state F. There is the state F. Let us open it up. Looks good. Make it a little smaller. I do not like that alternating color band. We are going to get rid of that in a minute anyway. Now I want to color code these. I want New York to be blue, Florida to be red, and California to be green. Right click. Design view. Let us open up the state abbreviation field. We are going to go to Format - Conditional Formatting. This is where we can set the rules for how to color this text box. New rule. If the value is equal to "New York," let us set the background color to a light blue. That is not a really great blue. Let us get a better blue here. There we go. Let us add another rule. New rule. If the value is equal to "California," let us set the color to green. One more, new rule, equal to "Florida," and let us go red. There are our three rules for our three states. You can add more. There is an upper limit; I do not know offhand what it is. I did a quick Google search and could not find one. I have never run into a limit, and I have had a lot of these in here. OK. Hit OK. Let us close that, save it, and reopen it again. There we go. Now we have a color-coded list. Now how can I get this value when I click on it back into my customer form? That is what we really want to do. I want to be able to click on this, have this form open, pick a value, and then have it populate over here. That is the trick. Let me close that. You can apply that same conditional formatting to this guy, by the way. Watch this. Open this up in Design View. Now click on that state abbreviation, go to the Format Painter, which is right there, and then click on State, like that. Save it. Close it. Now, when you open that up, did it get it? Let me see. Sometimes it does not get it. Let us try that again. Sometimes when you go across forms like that, I think it is a bug in Access. You cannot format paint easily across different forms. Sometimes what you have to do is cheat. Watch this. Copy this, paste it in here. Now, Format Painter, and paste down here on top of that guy. That should work. I have noticed this before. Save it. Close it. Now let us open it up. There we go. Sometimes you have to do that. It is a little trick. If you click on one thing and then try to format paste across a different form, it is a bit of work. You just have to copy the control, and then it works fine. Now, how can I open up this little guy here? When I double click on that state, this is going to involve a little bit of programming, but it is only one command. We can do it with a button too, but I like to teach Visual Basic. There is a button in the command wizard that you can use to open up a form, but I do not want to do that. Watch this. Open up this guy's properties. Go to events and find On Double Click. When I double click on that state, I want something to happen. Click the ... button, the builder button. That brings up the Visual Basic window. Do not pay attention to everything else that is in here. This is a template that I have used for some other classes, so ignore this stuff. What you care about is right here. It is the state double click. By the way, if a window opened up asking you what kind of builder you want, you want the code builder. I have that turned off too. The code builder puts you in Visual Basic. All you need is one line of code: DoCmd.OpenForm "StateF" That is it. Open up that state form. All you need, one line of code. Visual Basic is not scary. It is just a couple of simple commands and you can do all kinds of cool stuff. Close that. Close that. Close this. Save it. Yes. Now, from here, if I double click, I have opened up that state form, and you can move this in position wherever you want, like right there. Close. Double click. See? And close. Now, how do I get this value back to there? We are going to do the same trick. I am going to double click on this and it is going to populate that value there and then close this form. Watch this. Again, very easy, two lines of code this time. Go to state abbreviation, bring up its double click event, bring up its properties, on event, on double click. We are using that on double click event. Again, hit the ... button, code builder. Now, this guy does not have any other code in it yet. So we have two things we have to do. We have to say: Forms!CustomerF!State = State_ABBR That sets the value on the customer form. Now I am going to close the state picker form. I am going to say: DoCmd.Close acForm, "StateF", acSaveYes That closes the form. That is it. Two lines of code. This is not scary. Close that. Close this. Close that. Save changes. I like to close everything down. I reopen it. California, double click. Let us change it to Florida, double click. Look at that. Change it again, double click, make it New York, double click. See? That is how you open up a little form like that and then send the value back. A lot of times when people say they want a colored list box, that is really all they want - a colored list of options like this. Maybe this was a list of sales reps. In fact, I just recorded my Access Developer 16 class. This class does require a lot of programming, but I was able to get what looks like a colored list box inside of another form. This involves a lot more programming and a lot of techniques, but this is usually what people are looking for. Here is a list of sales reps, and they are colored based on their state. A lot more advanced. I covered this in my Access Developer 16 class. I will put a link below in case you really want that. But a lot of times when people are telling me they want a colored list box, this is usually good enough. You can color this list based on whatever criteria you want, pick a value, close it, and it goes right back to that form. So that is all for this TechHelp video. However, there is an extended cut for members where I will show you how to lock down that little pop-up box so that users cannot change those values. I will also show you how you can colorize that second column based on the value in the first one. As I said before, if you really want it to look like a list box inside of another form, I do cover this in my Microsoft Access Developer Level 16 course. I will put a link to that below the video. How do you become a member? It is easy. The join button is right there under the video on YouTube and you will get access to all of my extended cut TechHelp videos, live video chat sessions, and other perks. Once you click join, you will see a list of all the different membership levels. Thanks for watching this free TechHelp video. These videos will always be free. I will keep making them as long as you keep watching them. If you like this video, make sure you click on the like button and give me a thumbs up. If you think others will find it handy, make sure you subscribe to my channel and ring the bell. Click on that little bell and pick all. You will get email notifications whenever I release a new video. For more information, click on the show more link down below the video. YouTube does a pretty good job of hiding it, but there it is. You will get all kinds of other links for cool stuff. If you have not yet watched my Access Level 1 beginner course, it is three hours long and it is absolutely free. It is right here on YouTube and on my website. If you like Level 1, Level 2 is just a dollar, and again, for members, that is free. Want to see your question answered in a video like this? Just visit my TechHelp page. That is all for today, folks. Thanks for watching. QuizQ1. What is the primary limitation of a standard Access list box regarding formatting?A. It only allows one uniform color for all items B. It can set individual colors per row C. It supports rich text formatting D. It allows for images in each row Q2. Which Access control shares the same color limitation as the list box? A. Command button B. Combo box C. Subform D. Chart control Q3. If you want a list where each item can have different colors, what is Richard's recommended alternative to a standard list box? A. Use a report object B. Use a continuous form C. Use a pivot table D. Use only combo boxes Q4. What is the main advantage of a continuous form over a single form in this context? A. It allows only one record to be shown at a time B. It allows seeing multiple records in a list format C. It disables editing records D. It supports only images, not text Q5. How does Richard suggest applying color formatting to individual records in a continuous form? A. With conditional formatting rules B. By changing the default view in Access options C. Using SQL queries in VBA D. Setting tab order Q6. What VBA code does Richard use to open a form named "StateF" on a double-click event? A. DoCmd.ShowForm "StateF" B. Forms!StateF.Open C. DoCmd.OpenForm "StateF" D. OpenForm("StateF") Q7. What action must be performed after selecting a value from the continuous form to send it back to the customer form? A. Write two lines of VBA code B. Use macros exclusively C. Drag and drop the value manually D. Use a hyperlink in the form Q8. How does Richard transfer the selected state back to the main form and close the picker form? A. Through two lines of code: setting the main form field and closing the picker form B. Through a data macro on the state table C. By running a query D. By exporting to Excel and re-importing Q9. What is a frequent misconception people have about Access list boxes, according to Richard? A. That they can store images directly B. That they support conditional formatting like continuous forms C. That they refresh automatically without any code D. That they support drag and drop Q10. Where does Richard say you can find extended tutorials on more advanced topics like embedded colorized list boxes? A. In Access Level 1 free course B. In the Access Developer Level 16 class C. Within standard Access help files D. On Microsoft's default support page Answers: 1-A; 2-B; 3-B; 4-B; 5-A; 6-C; 7-A; 8-A; 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. SummaryToday's video from Access Learning Zone tackles a popular question I often receive: is it possible to display different colors and formatting for individual rows in a Microsoft Access list box? This specific question came from Simon in Oregon, who wanted to know if the built-in list box control can display each item with its own color.The answer is simple: unfortunately, no. The default Access list box only supports a single, uniform color for all items. While you can specify a different color for the currently selected item, every other item in the list must share the same color. There are no built-in features to color individual rows, nor does Access provide conditional formatting for list boxes. This limitation is also present in combo boxes. For example, suppose you want to create a list box to select a state from just a few options, such as New York, Florida, and California. You can add these values to either a list box or a combo box, and tie the selection to a field in your table. The control will display the correct selection for each record. However, if you try to apply formatting so that each state appears in a different color—such as blue for New York, red for Florida, and green for California—you will quickly find that you cannot. Any formatting change you make will affect the entire list box, not individual items. This is a stumbling block for many users who want to visually distinguish items in a list for one reason or another, such as highlighting a sales representative's region or a customer's discount bracket. The good news is that there are workarounds if your real goal is to present a colorized list of selectable options. One of the best solutions in Access is to use a continuous form rather than a standard list box. Continuous forms allow you to display multiple records at once, making them look similar to a list box but with much greater formatting capabilities. To create this, I start by setting up a table for my state list, including short text fields for the state abbreviation and full state name. For small lookup tables like this with just a few options and no anticipated growth, it is acceptable to use the abbreviation as the primary key, and this keeps things simple. Once the data is entered, I design a continuous form bound to this table. After adjusting the layout to show just the fields I need (such as abbreviation and state name), I can move on to formatting. Access provides conditional formatting for text box controls on continuous forms, so I can apply rules to display each state in a different color. For example, I set up rules so that rows where the value is "New York" appear with a blue background, "Florida" appears in red, and "California" appears in green. You can add as many rules as you need, and for most cases you'll never hit the upper limit. With the formatting in place, opening the continuous form now displays each available state with its own color, just as desired. The next challenge is to allow the selection from this colored list to update a field on another form, such as assigning the chosen state to a particular customer. To achieve this, I add simple Visual Basic code. When a user double-clicks the state field on the customer form, the state selection form opens. When the user double-clicks an item in the selection form, a couple of lines of VBA will set the customer form's state field to the chosen abbreviation, then close the selection form. This method requires only basic VBA: one line to assign the value to the field in the calling form, and another line to close the selection window. Occasionally, copying conditional formatting between forms can be tricky. Using techniques like the Format Painter may not always work across forms due to quirks in Access. If you encounter trouble, try copying and pasting the formatted control directly instead. This process can easily be adapted to other scenarios. For instance, you could use the same principle for lists of sales reps, product categories, or anything where you want to visually cue users with colors or formatting. In my Access Developer 16 course, I cover more advanced techniques for embedding what looks like a colorized list box inside another form, offering even more flexibility, though this does require more advanced programming. For most users, building a simple continuous form with conditional formatting will meet their needs for a color-coded, selectable list. Users can choose an item, and the form will handle returning the value to where it is needed. For those interested in further enhancing the solution, I offer an extended cut for members. This extra segment covers how to lock down the pop-up state picker so users cannot make unauthorized changes. I also demonstrate how to apply color formatting based on values in other columns. If your requirements demand that a control truly looks and acts like a list box embedded on a form with full color support, that is addressed in detail in my Microsoft Access Developer Level 16 course. You can find the link for that below. As always, becoming a member is easy, and brings access to all my extended cut TechHelp videos, live sessions, and more. Details about the various membership levels can be found under any video. Thank you for watching this free TechHelp video. I will continue producing these as long as you keep watching and supporting the channel. If you liked this lesson, make sure to subscribe and turn on notifications so you will not miss future tutorials. Additional details, links, and resources are provided below the video. If you are new to Access, my Level 1 beginner course is available for free on both YouTube and my website. Level 2 is just a dollar, and is included free for members. If you have a question you would like answered in a future video, just head over to my TechHelp page and submit it. 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 ListLimitations of formatting in standard Access list boxesCreating a state table with abbreviations Designing a state picker continuous form Binding a continuous form to a record source Removing alternating row colors in forms Applying conditional formatting to color-code rows Copying conditional formatting between controls Using the On Double Click event to open a form Writing VBA code to open forms Transferring values between forms with VBA Closing forms with VBA after selection Using color-coded continuous forms as list box alternatives |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access list box listbox formatting color formatted combo box formatted combo PermaLink List Box with Format and Color? in Microsoft Access |
