Access Developer 40
Conditional Formatting VBA, Color & Font Pickers
Welcome
Today's class is going to be focusing on changing Conditional Formatting using VBA. Now this might not sound very impressive, but you can have your user set up custom color schemes for things like forms and reports. We'll learn how to work with the Windows Color Picker dialog, where you can click a button, it'll open up the color palette. You can select the color and save that in your table. And we'll learn how to work with the Windows Font dialog box as well. We're going to build a countdown timer form, which shows you how many days you have left until certain events are coming up.
Resources
Topics Covered
In Lesson 1, we're going to build an event countdown form. So if you've got some events coming up, conferences, beach day, some surgery, whatever this will pop up whenever your database loads to remind you, "Hey, you got this conference coming up tomorrow." Or "you have to go to the beach in four days." We'll create the display form, a little conditional formatting for events that are coming up soon. One day is red, less than five days will be yellow. We'll make buttons to add, edit, or delete events using code. We'll make a little form to edit those events, to pop up form.
In Lesson 2, we're continuing on with our event countdown form. We're going to add some conditional formatting. We're going to let the user pick a color scheme so they can choose red, blue, black, green, and that will be applied for each of the events.
In Lesson 3, we're going to build a custom color editor. It's a form to edit our color scheme. We'll learn how to work with hex and RGB values and then convert between them and to use the windows color dialog. So the user can select the color and then save it in our color scheme form.
In Lesson 4, we're going to begin working with the conditional formatting VBA code. We'll learn about the FormatCondition, object, the FormatConditions collection, how to delete and add them. We'll determine the maximum number of conditional formatting items that you can have. Then we'll count the records in the current form, so we don't add too many of them and generate errors. And I'll show you how to do that with something other than DCount.
In Lesson 5, we are continuing with the conditional formatting VBA. We'll add the additional controls on the form to the conditional formatting loop. That number box, the background box. We will add some code to open up a window to the right of another form. So for example, we always want the event countdown form opening up to the right of the main menu form, no matter where it is. Then we'll refresh events from the color form. So you got the color form open. You refresh that it'll close and reopen the event countdown form. So you can see the updates in real time. And then we're going to see some updates to the color picker and the RGB and hex functions. And we'll explore those in the code vault.
In Lesson 6, we're going to learn about user defined types. We'll learn about what user defined types are in VBA, how to define them, and how to pass a variable around that is a user defined type and how it always uses ByRef.
In Lesson 7, we're going to learn how to use the Windows Font Dialog so that we can change fonts at runtime. The user can pick the font for the event and for the days left. You can use the same technique to pick the font on a report, for example. We'll learn how to save those changes in a settings table and then load them back up when the form loads
In Lesson 8, we're going to apply the font stuff that we learned in lesson seven and apply it to our letter writer. So we'll be able to specify a custom font for each letter. Each letter can have a different font if you want. You can save that in the letters settings in its record. And there are some issues when applying these fonts to reports, we'll cover that in lesson 8.
Enroll Today
Enroll now so that you watch these lessons, learn with us, post questions, and more!
Questions?
Please feel free to post your questions or comments below. If you are not sure as to whether or not this product will meet your needs, I'd rather help you before you buy it. Remember, all sales are final. Thank you.
 Keywords
microsoft access, access 2016, access 2019, access 2021, access 365, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, event countdown, conditional formatting vba, hextorgb, rgbtohex, formatconditions, recordcount, user defined type, byref, font dialog, color picker
Intro In this lesson, we will learn how to use VBA in Microsoft Access to change conditional formatting dynamically, allowing users to set custom color schemes and fonts for forms and reports. Topics include working with the Windows Color Picker and Font dialog boxes, building an event countdown form with customizable alerts, creating a custom color editor to handle RGB and hex values, and using the Format Condition object in VBA. We will also cover user defined types, managing conditional formatting limits, and saving user preferences in tables for future use. This is Microsoft Access Developer Level 40.Transcript Welcome to Microsoft Access Developer Level 40 brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
Today's class is going to focus on changing conditional formatting using VBA. This might not sound very impressive, but you can have your user set up custom color schemes for things on forms and even reports.
We will learn how to work with the Windows Color Picker dialog, where you can click a button that will open up the color palette. You can select the color and save that in your table. We will also learn how to work with the Windows Font dialog box.
We are going to build a countdown timer form, which shows you how many days you have left until certain events are coming up. That is how we will give the user the ability to set custom fonts and custom colors for each of their custom color schemes.
There is lots of cool stuff in this lesson. This lesson follows Access Developer Level 39. I strongly recommend you have finished all of my previous classes, Beginner, Expert, Advanced, and Developer levels before continuing with today's class. My lessons are really designed to be followed one after the other.
For example, this one relies heavily on record sets, which are covered in Developer 16, so make sure you have that one under your belt as well.
This class is recorded with Microsoft Access as part of a Microsoft 365 subscription, which I strongly recommend. It is currently August of 2022. Also, if you are using the retail version, this is roughly equivalent to Access 2021. However, the lessons covered in today's class should work with any version of Access going back to 2007.
If you have any questions regarding the material covered in today's class, just scroll down to the bottom of that page and post a question on the page that you are on. Make sure you take a minute to read through any other questions that have been posted as your question may have already been answered. Make sure you click the subscribe button to get notified if any other questions or comments are posted for this class.
If you have any questions not related to today's class, but they are about Access in general, you can feel free to post them in my Access forum.
Now let's take a closer look at exactly what is covered in Access Developer Level 40.
In lesson one, we are going to build an event countdown form. If you have some events coming up, conferences, beach days, surgery, whatever, this will pop up whenever your database loads to remind you that you have this conference coming up tomorrow, or you have to go to the beach in four days. We will create the display form and add a little conditional formatting for if events are coming up soon. One day will be red, less than five days will be yellow.
We will make buttons to add, edit, or delete events using code. We will make a little form to edit those events as a popup form, and lots more.
In lesson two, we are continuing with our event countdown form. We are going to add some conditional formatting. We will let the user pick a color scheme so they can choose red, blue, black, green, and that will be applied for each of the events, whichever one you want.
In lesson three, we are going to build a custom color editor. It is a form to edit our color scheme. We will learn how to work with hex and RGB values and convert between them, and to use the Windows Color dialog so the user can select the color and then save it in our color scheme form.
In lesson four, we are going to begin working with the conditional formatting VBA code. We will learn about the Format Condition object, the Format Conditions collection, and how to delete and add them. We will determine the maximum number of conditional formatting items that you can have. Then we will count the records in the current form so we do not add too many of them and generate errors, and I will show you how to do that with something other than DCount.
In lesson five, we are continuing with the conditional formatting VBA. We will add the additional controls on the form to the conditional formatting loop, that number box, and the background box. We will add some code to open up a window to the right of another form. For example, we always want the event countdown form opening up to the right of the main menu form, no matter where it is. That is pretty cool code.
Then we will do the refresh events from the color form. You have the color form open, you refresh that, and it will close and reopen the event countdown form so you can see the updates in real time. We are going to see some updates to the color picker and the RGB and hex functions, and we will explore those in the code vault.
In lesson six, we are going to learn about user defined types. We will learn about what user defined types are in VBA, how to define them, and how to pass a variable around that is a user defined type, and how it always uses ByRef.
In lesson seven, we are going to learn how to use the Windows font dialog so that we can change fonts at runtime so the user can pick the font for the event and for the days left. You can use the same technique to pick the font in a report, for example. We will learn how to use the Windows font dialog, how to save those changes in a settings table, and then load them back up when the form loads.
In lesson eight, we are going to apply the font techniques that we learned in lesson seven and apply them to our letter writer. We will be able to specify a custom font for each letter. Each letter can have a different font if you want. You can save that in the letter settings in its record. There are some issues when applying these fonts to reports, and we will cover that in lesson eight.Quiz Q1. What is the main focus of Microsoft Access Developer Level 40?
A. Learning SQL language concepts
B. Creating custom Access startup screens
C. Changing conditional formatting using VBA
D. Building Access database relationships
Q2. What tool allows users to select colors and save them in a table?
A. Windows Task Manager
B. Windows Color Picker dialog
C. Access Startup Wizard
D. Table Design View
Q3. Which functionality will allow users to have custom color schemes for forms and reports?
A. Record Source property
B. Conditional formatting using VBA and user selection
C. Sorting and filtering data
D. AutoNumber fields
Q4. Which object and collection are important when handling conditional formatting via VBA?
A. Field object and Fields collection
B. Control object and Controls collection
C. Format Condition object and Format Conditions collection
D. Table object and Tables collection
Q5. What is a feature of the event countdown form created in lesson one?
A. It displays hardware diagnostics
B. It reminds users of upcoming events by showing days left
C. It imports data from Excel
D. It exports tables to PDF
Q6. How can users edit events in the event countdown system?
A. Using Table Design View
B. By typing directly in a query window
C. Via popup forms with add, edit, and delete buttons coded in VBA
D. With the Access Start Screen
Q7. What conditional formatting colors are applied for events coming up soon in the countdown form?
A. Green for one day, blue for less than five days
B. Red for one day, yellow for less than five days
C. Purple for one day, orange for less than five days
D. Blue for all upcoming events
Q8. What lesson teaches how to work with hex and RGB color values in Access?
A. Lesson two
B. Lesson five
C. Lesson three
D. Lesson seven
Q9. Which lesson introduces the concept of user defined types in VBA?
A. Lesson eight
B. Lesson six
C. Lesson four
D. Lesson two
Q10. The Windows font dialog allows the user to:
A. Choose database encryption settings
B. Choose the font for events and days left at runtime
C. Choose report grouping options
D. Change Access interface language
Q11. What happens when the color form is refreshed in lesson five?
A. Application restarts
B. Event countdown form is closed and reopened to display updates
C. All forms are minimized
D. Access generates an error
Q12. Why is it suggested to take previous courses before Level 40?
A. To receive a course discount
B. The lessons build upon concepts from earlier courses
C. The older courses contain more advanced material
D. Access 2021 is required only after Level 40
Q13. How are custom font selections for letters handled in lesson eight?
A. All letters share the same default font
B. Each letter can have its own custom font setting saved and reloaded
C. Font changes are not allowed in letters
D. Fonts can only be changed for reports, not letters
Answers: 1-C; 2-B; 3-B; 4-C; 5-B; 6-C; 7-B; 8-C; 9-B; 10-B; 11-B; 12-B; 13-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 changing conditional formatting in Microsoft Access using VBA. While this might sound basic at first, it actually opens up powerful customization options, including allowing users to set their own color schemes for different elements on forms and even reports.
A big part of this course involves using the Windows Color Picker dialog box. With this tool, users can select colors from a palette, and those choices can be saved directly in your database tables. We will also cover how to use the Windows Font dialog box to give users even more control over the appearance of their data.
To demonstrate all of these techniques, we are going to build a countdown timer form. This form will display how many days remain until important events, like an upcoming conference or vacation, directly when the database loads. Users will have the option to set custom fonts and colors for each event, so every event can have its own style.
This lesson is packed with useful tips and builds on concepts from Access Developer Level 39. It is very important that you have completed all previous classes - Beginner, Expert, Advanced, and Developer - before tackling this material. My courses are designed to be completed in sequence, and knowledge from earlier lessons - especially on topics like recordsets from Developer 16 - is crucial for understanding the material this time around.
Everything in this class was recorded using Microsoft Access with a Microsoft 365 subscription, which I highly recommend. The recording date is August 2022, and if you are using the retail version, Access 2021 is roughly equivalent. Still, most of what we cover today will work with versions of Access going back to 2007.
If you have questions about anything in this lesson, scroll down to the bottom of the page and ask your question right there. Be sure to review any previously asked questions as someone may have already addressed your concern. Subscribe for updates if you want to be notified when new questions or answers are posted. For questions outside the scope of today's class but still related to Access, please use the Access forum on my website.
Let me walk you through what is covered in each lesson of Developer Level 40:
Lesson one introduces the event countdown form. This feature will remind you about upcoming events as soon as you open the database. We will build the interface, add basic conditional formatting to highlight events happening soon, and set up code to add, edit, or delete events through a popup form.
Lesson two continues the countdown form, enhancing it with more sophisticated conditional formatting. Here, we will let users pick a color scheme for each event, choosing from options like red, blue, black, or green.
Lesson three is all about building a custom color editor within Access. I will show you how to handle hex and RGB color values, convert between them, and let the user select colors with the Windows Color dialog. These colors will be saved in your own color scheme form.
Lesson four gets into the nuts and bolts of handling conditional formatting with VBA. This includes working with the Format Condition object and the Format Conditions collection. We will look at how to delete and add new formatting rules, determine the maximum number allowed, and count records on a form in a way that avoids errors, using techniques beyond just the DCount function.
Lesson five builds on this by looping through all the controls on a form that need conditional formatting - such as number boxes and background boxes. We will write code to position the event countdown form relative to the main menu form, ensuring it always opens where you want it. I will also show you how to refresh the event display in real time after making changes to color schemes and how to update the color picker and associated functions.
Lesson six introduces user defined types in VBA. I will explain what user defined types are, how to define them, and how to pass them as variables. You will also learn about how these types are always passed ByRef.
Lesson seven dives deeper into customization, showing you how to use the Windows Font dialog so users can change fonts at runtime. We will save these font settings in a table so they persist between sessions and reload them when the form starts.
Lesson eight applies the same font-selection techniques to the letter writer. Here, you will be able to set a unique font for each letter, save that preference with the letter's record, and handle any issues that come up when applying fonts to reports.
If you would like to see a complete, step-by-step video tutorial on everything we covered here, visit my website at the link below. Live long and prosper, my friends.Topic List Building an event countdown form in Access
Adding conditional formatting to highlight upcoming events
Creating buttons to add, edit, and delete events with VBA
Editing events using a popup form
Allowing users to select color schemes for events
Building a custom color scheme editor form
Converting between hex and RGB color values
Using the Windows Color Picker dialog in Access
Saving selected colors to a table
Working with Format Condition objects in VBA
Managing the Format Conditions collection
Determining and limiting the number of conditional formats
Counting records efficiently to prevent errors in formatting
Adding multiple controls to a conditional formatting loop
Opening forms dynamically positioned relative to other forms
Refreshing event and color forms to show live updates
Updating color picker and RGB/hex conversion functions
Defining and using user defined types in VBA
Passing user defined types by reference between procedures
Using the Windows Font dialog in Access at runtime
Saving and loading font settings from a table
Applying custom fonts to forms and letter writer records
Handling issues with custom fonts in reports |
