Check Box Color
By Richard Rost
4 years ago
Change the Color of a Check Box in Access
In this Microsoft Access tutorial, I will show you a trick for changing the "color" of a checkbox by using Conditional Formatting and an extra text box. We'll also learn how to use a little bit of VBA to change the color of the label attached to the check box.
Pre-Requisites
Recommended Courses
Links
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, change color of checkbox, check box, backcolor, conditional formatting, border thickness, border color, change the background color of a check box, CheckBox.BorderColor, CheckBox.BackColor, Conditional Formatting for checkboxes, yes/no
Intro In this video, I will show you how to create the effect of changing the color of a checkbox in Microsoft Access, even though Access does not allow you to alter the actual checkbox color directly. We will look at creative workarounds, including using a colored text box behind the checkbox with conditional formatting and a little bit of VBA to enhance usability, as well as using VBA to dynamically change the background color of an attached label to indicate the state of the checkbox. If you want visually distinct Yes/No fields in your forms, this video will show you some practical solutions.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 color of a checkbox in Microsoft Access. Actually, technically, the title of the video is a little misleading because you can't change the color of the actual checkbox itself. It's a limitation of Access. I wish the Microsoft guys would change that, but we have to deal with it. But I'm leaving that as the title because I know that's what everyone's searching for. So when you search for how to change the color of a checkbox in Microsoft Access, I want people to find this video so they can get help.
Here's our lonely checkbox right here: Is Active. You can click on the label part or the checkbox part. Checkbox part - what did I just say? The check box part. Say that ten times fast: checkbox part.
The label that's attached to it can also be clicked on. You can't change the color of that, and it's different based on your Windows settings, different versions of Access, different versions of Windows and Office. This guy might look a little different. I know in older versions it was gray. Mine looks blue.
If you click on it and go up to Format, there's nothing you can do. You can't change the colors over here. There's a shape outline, but it doesn't seem to do anything. You can make it red. You can change the line thickness, make it thicker. I've played with all the different properties in here and none of them seem to work. There are no theme settings, so you can't use themes like you can with some of the other controls.
In VBA, there is no back color property for it. If you know VBA, you can't say, for example, "mycheckbox.backcolor =" and change it. There's no way to change the actual checkbox itself. You can change the label, but you can't change the checkbox. You can't even apply conditional formatting; conditional formatting is grayed out.
So what do you do? Well, there are a couple of things you can do. I'm going to show you two things.
One is a trick that doesn't involve any programming at all. It just uses conditional formatting. The second trick does use a little bit of programming and involves modifying the label.
Let's do the non-programming method first. This actually works better if you do it in continuous forms. You can use it in single forms if you want to, but I think it looks nicer in continuous forms.
Go to design view, and we don't need all these fields. I'm just going to remove some of these. All I want in here is a customer ID, for example, and last name. Let's put the Is Active field here. All right, there's Is Active. It's a Yes/No field. It comes in as a checkbox. I'll just delete that label.
Now, I'm going to leave it bigger for now so I have some room to work. We'll shrink it up in a minute. Put a label above it if you really want to label it. "Active." There you go.
What this involves is putting a text box behind the checkbox, and the text box will give it a colored background. It'll look like this from my title slide. When you check the box, it'll go green. When you uncheck it, it goes red. Pretty neat looking. This is as close as we can get to actually changing the color of the box itself. This doesn't involve any programming, just some conditional formatting tricks.
Take a text box. You can borrow one of these if you want - copy, paste, slide it over here. Then right-click on it, go to Position, and Send to Back, so it's behind the checkbox.
While you have it, go to its properties. Bind it to the same field. Bind it to Is Active. Then give it a good name. We'll call it IsActiveText. It's active because you can't have two controls with the same name, but they can have the same control source; that's where they're bound to. The checkbox and that text box are both bound to Is Active.
We're not going to change this guy. We just need a different conditional formatting. Go to Data. Then we're going to lock it and disable it so the user can't actually change the value in here.
Then shrink this up so it's about yay big, just to sit behind the checkbox like that. You can make this as big or as little as you want. If you want to make it a little bit larger, see if you can get it to click, then make it a bit bigger like that. Select them both together and slide them up into there. That's kind of nice.
Now, click on the text box. If it's too hard to see, just move the checkbox out of the way for now. Click on the text box. One more tab, too. We're going to go over here to another property as a Tab Stop. We don't want to make it a tab stop. Go to the Other tab and change Tab Stop to No. If the user is tabbing, we don't want to stop there. We'll just keep passing by.
Now all we do is change the conditional formatting of this guy. Go up here. If you don't know what conditional formatting is, go watch this video. It's basically changing the colors and properties in fields based on the data in those fields or other fields in the same record. Go watch this if you've never done conditional formatting before.
For this field: Conditional Formatting > New Rule > Field Value is equal to 0 (False). We're going to set the background color to red and the foreground color also to red. They have to match because you don't want to see any text in there; we just want the color. Hit OK. That is the red one.
Add another rule: New Rule > Field Value is equal to... now remember, Yes in Access is -1. For SQL Server, it's 1. For Access, it's -1. That's the subject for a different video.
Let's pick green for foreground and background - same green color. Hit OK. There's red and green, very Christmasy. Apply, hit OK.
Now, slide that checkbox back on top so it looks like that. Slide it over a little bit like that - a little bit more. Bring this over and bring that up. Save it. Close it. Open it back up again. Ready? Boom. There it is. Simple conditional formatting. Check the box. On. Off. Your tabbing just keeps going by that text box.
The only problem you run into this way is if the user somehow gets inside there and clicks. But we can fix that with a tiny bit of VBA. We can just say when this guy gets the focus, put the focus on the checkbox.
Watch this: Design view. One little line of code. If you've never done any VBA programming, don't worry. Watch this video, it's real easy. It's about 20 minutes long, and I teach all the stuff you need to know. Just one line of code in the right spot sometimes can make all the difference.
For this one, we need to come in here and get the... Sometimes it's hard to click on it, so you might have to move it the other way and then do your work. Open up the properties for that. Go to Events and find "On Got Focus." If this gets the focus, we're going to do something. Click the button. We're going to say:
IsActive.SetFocus
In other words, if the IsActiveText gets the focus, immediately set the focus to that other field.
Save it. Close it. I forgot to put it back - well, this is a good test. If I click here, boom, it moves right there immediately. See that? Click here, boom, it moves right there in the same record. So that fixes that problem.
One little line of code. Now you can slide it back over. Let me see the rest. Let's go right there. No, missed it again. There. Perfect.
And you could even change the value, too. If they click there, it will put the focus there, but it's not going to actually click it. Here, let me show you.
If I click here, if I go there, it just moves the focus, but it doesn't change the button. See, if I click right there... So what we could do is if that happens - if they click in there and get in that spot - we could set the focus there and then:
IsActive = Not IsActive
In other words, set it to whatever it's not, as if they checked the box.
Come back out here. Close it. Try again. Click on it. I'm going to try to get right there. Click. And it does it. See? It fixes that little problem. That's only if they manage to get in that little tiny space next to the box. We solved the problem, didn't we?
So that's one thing you can do, and that's minimal programming. It works without any programming; that line of code is just a little bit of a fix.
Let's go back to our single form, though. What you can do with this is leave the actual checkbox alone and just change the label that it's attached to, just like I showed you in the title slide here. Green and red for whether it's active or not. This does involve a little bit of programming, but not much.
Go into design view. Find this guy. Go to its After Update event. If you've never done an After Update event before, watch this video. It's my After Update video. It's basically something that happens when the value of a field is changed. When you click on a checkbox, it changes its value. So we're going to do some stuff. And while you're at it, watch my If Then video, because we're going to have to do an If...Then. We're going to say: If it's this, then do that.
Here's what we're going to say. First, I forgot something. I forgot a step. I always forget steps and then go back. Go back over here. This label. We didn't give it a name. I don't generally name my labels unless I'm going to do something to them, because for every field that you get, you get a label with it that's attached to it. So let's give this guy a good name, because I don't want to refer to it as Label12 in my code. Come over here to "All," find the Name, and call it IsActiveLabel. No spaces in our field names. Many times people have problems because they've got spaces in their field names.
Try again. Come back over here. If IsActive, then (basically that's shorthand for "If IsActive = True") then IsActiveLabel.BackColor. See, the label does have a BackColor; checkboxes don't. Set the BackColor to vbGreen. If you want to use an exact RGB color, you can say RGB and then put a number in here. That's a different video though. I'll just go with vbGreen, it's easier.
If I say IsActive dot - there is no BackColor. You can't even pick it.
Hey, Access guys at Microsoft, do more with checkboxes. Give them a BackColor. But I wouldn't be able to make all these cool videos if you fixed all the problems that I have to teach people how to get around. It's keeping me in a job.
So, if IsActive, then we're going to make it green. Otherwise (Else) IsActiveLabel.BackColor = vbRed. End If. Save it.
Come back out here. Close that. Open it up. Click. Click. Hang on, what's going on here? Oh, look at that. I forgot about this. The first time I did this, I had this problem too. The color actually is changing. Why don't you see it? Well, because the transparent setting is on. By default, all these labels are transparent - see how you can still see the grid lines behind there, the dots? That's a transparent label. So it matches the background color of the form. That's by design.
There are a couple of ways you can fix it. You can go into the properties, or you can just come up here and give it a background color. Let's give it Shape Fill. Let's just neutrally set it to gray. Then I'm going to make it go right up against that label or the checkbox like that and center it, so there's a little space around it.
Save it. Close it one more time. Now it's going to start off as gray. We'll fix that in a minute. But watch when I click on it: click, red, green, red, green. Look at that. That's an After Update event right there. So that's kind of neat.
One more thing we have to do, though, is we've changed it in the After Update event, but there's another event we have to be aware of, and that's the On Current event. As I move from record to record, notice it doesn't change. Or if I close it and reopen it, we're back to gray. So we also have to have that event run when we go from record to record or when the form opens - that's the On Current event.
Want to learn more about it? I have a video for pretty much everything.
So, go into the form's properties, go to Events, go to On Current, and go right here. Now, the code we need is down here. I could copy and paste it, but I don't like having duplicated code in multiple places. So there are two things we could do. We could say that - in other words, the form current event is going to call the After Update event for the IsActive field. But I don't like doing that either, because sometimes I come in here and I want to make changes to this. I might forget that it's also up here, so it might affect this as well. So what I like to do is make this its own subroutine.
I'll say here, "Private Sub ChangeIsActive," or whatever you want to call it. Now both of those will call this. See, ChangeIsActive. And this one will call ChangeIsActive as well. See that? So now the After Update event for the control and the form current event both call ChangeIsActive.
This is the kind of cool stuff that I teach in my Developer classes. So if you want to learn this cool VBA stuff, start with my Access Developer 1 class.
Now we can save that, close it, close this. I always close my forms and reopen them between testing.
Open it up. Notice it's immediately red. Move from record to record; that one goes green. See?
And yeah, I know it's not changing the color of the checkbox itself, but you can't. There's no way to do it. Well, there's no easy way to do it.
In a related video, if you want to learn how to do more with checkboxes, I've got another video called "Large Check Box" where we take the standard checkbox and make it look like this. We actually replace it with a big checkbox, and that can look pretty cool. You can change this too, when you check on it if you want. Go green, for example.
If you want to learn more about conditional formatting, I cover a lot more about it in my Access Expert Level 19 class. There are all kinds of additional advanced conditional formatting expressions in there. Plus, there's a bunch of other stuff like mail merge into Word and all that sort of stuff. But this is my big-time conditional formatting class.
If you want to get serious about VBA programming for Microsoft Access, my Access Developer Level 1 class covers all the basics and goes into a lot more detail than that free VBA class I've got. It's a great place to get started.
I've currently got 40 Developer lessons. I make a new one every month or so. There's lots and lots to cover, and there's lots to learn. I've been doing this for so many years, I don't remember what's covered in each class. I have to look at the outline myself.
But if you want to learn about programming VBA for Access, this is where you start.
So there's your Fast Tip video for today. I hope you learned something, and we'll see you next time.
If you have any cool solutions for how to get your checkboxes formatted pretty that I missed, let me know. Post them in the comments below. I'm always interested in learning tips and tricks from you. I learn at least one new thing every week that I didn't think of or didn't know, and a student sends it to me in a comment or posts it in my forums. I love learning this stuff. So please, by all means, if you know something I don't, I want to hear about it.
Take care.
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. 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 that will be shown in each video as long as you are 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 in Microsoft Access regarding checkbox color customization?
A. You cannot change the color of the actual checkbox itself
B. You can only change the outline color of the checkbox
C. You can change the background color with themes
D. You can use conditional formatting directly on the checkbox
Q2. Which part associated with a checkbox in Access can have its color changed easily?
A. The actual checkbox control
B. The Windows background color
C. The label attached to the checkbox
D. The gridlines in the form
Q3. What is the primary technique suggested in the video to simulate a colored checkbox using no code?
A. Use conditional formatting on the checkbox directly
B. Put a text box behind the checkbox and use conditional formatting
C. Apply a custom theme to the checkbox
D. Use a macro to toggle colors
Q4. Which property of the underlying field should both the checkbox and text box share in the suggested workaround?
A. The same name
B. The same bound control source
C. The same label
D. The same tab order
Q5. What should be done to the textbox to prevent users from editing it in the workaround?
A. Remove the control source
B. Hide the textbox
C. Set it as locked and disabled
D. Make it read-only only when the checkbox is checked
Q6. What type of Access form is the no-programming colored background trick especially effective in?
A. Report view
B. Print preview
C. Continuous forms
D. Pivot table forms
Q7. In Access, what are the numeric values of Yes and No for a Yes/No (Boolean) field, as mentioned in the video?
A. 1 and 0 for both Access and SQL Server
B. -1 for Yes and 0 for No in Access
C. 1 for Yes and 0 for No in Access
D. -1 for No and 1 for Yes in Access
Q8. What line of VBA code is used to move the focus back to the checkbox if the user accidentally focuses on the text box behind it?
A. Me.TextBox1.SetFocus
B. IsActiveText.SetFocus
C. IsActive.SetFocus
D. FocusControl("IsActive")
Q9. How can you make clicking the background text box toggle the checkbox value with VBA?
A. Assign IsActive = True in the On Click event
B. Set IsActive = Not IsActive in the Got Focus or Click event
C. Assign IsActive.Value = 1 in the On Dirty event
D. No code can make this work
Q10. When programming the label color change for the checkbox, which event should you use to respond to checkbox value changes as the user edits the value?
A. On Load event
B. Mouse Click event
C. After Update event
D. On Format event
Q11. Why do you also need to handle the On Current event when changing label color?
A. It runs when the value of the field changes
B. It runs when a new user logs in
C. It triggers when moving between records to update the display
D. It only runs when the form loads initially
Q12. What programming best practice is recommended regarding updating multiple events with the same code?
A. Copy and paste the same code in each event
B. Use macros for one event, VBA for another
C. Write a separate subroutine and call it from both events
D. Avoid updating both events
Q13. What is a common issue with Access labels that prevents color changes from appearing?
A. The label's background is always white
B. The label is transparent by default
C. The label cannot be renamed
D. The label is not bound to any control
Q14. What happens if you only set the label background color in the After Update event but not in the On Current event?
A. The label color will always match the current field value
B. The label color will not update when moving between records
C. The form will not open
D. The checkbox will stop working
Q15. According to the video, what is the only thing you can reliably change the color of without VBA when working with a checkbox in Access?
A. The checkbox tick mark
B. The checkbox border
C. The background of a text box behind the checkbox using conditional formatting
D. The label's foreground color
Answers: 1-A; 2-C; 3-B; 4-B; 5-C; 6-C; 7-B; 8-C; 9-B; 10-C; 11-C; 12-C; 13-B; 14-B; 15-C
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 covers a common question many Access developers run into: how can you change the color of a checkbox in Microsoft Access? I'm Richard Rost, your instructor, and today I'll walk you through what is possible and what isn't due to Access's limitations.
First, let me set expectations. Unfortunately, Access does not allow you to change the actual color of the checkbox control itself. This has been a limitation in Access for quite some time, and although it would be great if Microsoft updated this feature, we have to work with what's available. I realize this is a topic many people search for, so I want to make sure you can find the right solution even if it isn't exactly what the title suggests.
If you've tried it yourself, you'll notice that with checkboxes, you do not have the option to alter their colors either through the Format tab or any property settings. Adjusting shape outline or line thickness has no effect, and themes, which work with other controls, are not available for checkboxes. Even if you turn to VBA, you'll find there is no BackColor property, so you cannot programmatically change the color either. Conditional formatting also isn't an option here since it's disabled for checkboxes. While you can change the label attached to the checkbox, the checkbox itself cannot be styled directly.
So what can you do if you really want some visual indicator that changes color when a checkbox is checked or unchecked? I'll show you two methods. The first uses a workaround with conditional formatting and does not require any programming. The second involves a small amount of VBA to enhance the label associated with the checkbox.
Let's start with the no-programming method. This approach is especially effective in continuous forms but works with single forms as well. The idea is to use a text box behind the checkbox to add a colored background that matches the status of the checkbox—green for checked and red for unchecked.
To set this up, go into Design View and set up your form with the fields you want. Take your Yes/No field, use it as a checkbox, and delete its label if you don't need it. Next, add a text box and send it behind the checkbox using the Position options. Bind this new text box to the same field as the checkbox. Let's name it something unique like IsActiveText. Now both the checkbox and the new text box are bound to the same field but have different names.
Since we don't want users editing the text box, lock and disable it in the properties. Resize it so it fits neatly behind the checkbox, creating a colored background illusion.
To prevent users from tabbing into this text box, set its Tab Stop property to No. Now, with the text box selected, apply conditional formatting: set one rule for when the value is False (0), so the background and foreground are both red (which hides any text), and another for when it is True (-1 in Access), to use green for both. The text box will now display red or green depending on the checkbox state.
Move the checkbox back on top so it aligns with the new colored area. When you open the form now, you should see the background color change as the checkbox is toggled. Tabbing will skip over the hidden text box.
There's one small usability issue: if the user manages to click inside the colored text box behind the checkbox, it could disrupt the experience. To fix this, you can add a simple VBA line in the On Got Focus event of the text box: shift the focus immediately to the checkbox. If you want the click to actually toggle the value of the box, you can add a line that sets the checkbox value to its opposite each time the text box gets focus. This is only a minor addition and is easy to implement, especially if you've never used VBA before.
Now for the second method, let's turn to using VBA with the label. Here, you keep the checkbox as it is, but programmatically change the BackColor of its attached label based on the field's value. First, name the label something meaningful, like IsActiveLabel. Labels do have a BackColor property that can be configured in code, unlike the checkbox itself.
In the After Update event of the checkbox, use a conditional test: if checked, set the label's BackColor to green; otherwise, set it to red. Remember that labels are transparent by default, so you will also need to set a background color to make this work visually.
Also, make sure to update the color whenever the form moves to a new record or opens. Use the form's On Current event to call the same code that changes the label's color. To keep things organized, it is a good practice to use a separate subroutine and call it from both events. This way, you don't repeat your code in multiple places.
Upon saving and testing, you'll find that the label color will update properly as you move between records or change the checkbox's value. While the color of the checkbox itself remains unchanged, this gives you a strong visual cue based on the value.
For those interested in additional creative solutions, I have another video where I show you how to create a much larger custom-looking checkbox. This method replaces the standard checkbox control and lets you achieve an appearance that stands out more.
If you want a deeper understanding of conditional formatting and see what else you can do in Access, check out my Access Expert Level 19 class. There, I cover a wide range of conditional formatting techniques and more.
For those looking to develop more advanced skills in VBA programming for Access, my Access Developer Level 1 class is the place to start. It covers basics and many important concepts for building your own solutions.
I've published a large collection of classes for Access developers. New lessons are added regularly, covering a wide variety of topics and skill levels.
This covers today's Fast Tip. If you have your own ideas or solutions for getting visual indicators with checkboxes in Access, I'd love to hear from you. Please share your insights in the comments. I'm always looking to learn from your experience.
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 Limitations of customizing checkbox colors in Access
Understanding the default appearance of checkboxes
Exploring the Format options for checkboxes
Using a text box behind the checkbox for background color
Binding a text box to the same field as the checkbox
Applying conditional formatting for color changes
Locking and disabling the background text box
Adjusting Tab Stop settings for seamless navigation
Setting conditional formatting rules for Yes/No values
Aligning and layering controls for visual effect
Adding VBA to redirect focus from the text box to checkbox
Using VBA to toggle checkbox value when background clicked
Renaming controls for clean code referencing
Using After Update event to change label color with VBA
Setting label background color based on checkbox value
Handling label transparency properties in Access
Implementing On Current event to update label color by record
Creating a shared subroutine for repeated code execution
Demonstrating form navigation and color updates in real time |
