Introduction

Welcome! Format, Colors, Fonts via VBA

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Developer Level 40. In this course we will focus on changing conditional formatting using VBA, building an event countdown form with custom fonts and color schemes, working with the Windows Color Picker and Font dialog boxes, applying conditional formatting to form controls, managing FormatCondition objects, using user defined types in VBA, and saving user settings in a table. We will also discuss extending these techniques to reports and a letter writer so each item can have its own custom font and color settings.

Navigation

Prev: D40
Next: Lesson 01

Keywords

Access Developer, conditional formatting VBA, color picker dialog, font dialog, custom color schemes, event countdown form, recordsets, FormatCondition object, FormatConditions collection, RGB values, hex values, user defined types, settings table, report

Subscribe to Introduction
Get notifications when this page is updated

Intro

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 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 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 as well.

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 the custom fonts and the custom colors for each of their custom color schemes. 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 recordsets, which is 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's 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. And 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. So if you have some events coming up - conferences, beach days, and surgery, whatever - this will pop up whenever your database loads to remind you, "Hey, you have this conference coming up tomorrow," or, "You have to go to the beach in four days." We will create the display form, with a little conditional formatting for if something is coming up soon. One day is 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, a pop-up form, and lots more.

In lesson two, we are continuing on with our event countdown form. We are going to add some conditional formatting. We are 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, 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, convert between them, and 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 FormatCondition object, the FormatConditions 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: the number box, 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. If 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. Then 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, 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, we will learn 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 stuff that we learned in lesson seven and apply it 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 letters settings in its record. There are some issues when applying these fonts to reports; we will cover that in lesson eight.

Quiz

Q1. What is the main focus of Access Developer Level 40?
A. Changing conditional formatting using VBA
B. Creating queries using SQL
C. Designing reports only
D. Importing data from Excel

Q2. What will users learn to work with in this class regarding colors?
A. Only basic color names
B. Windows Color Picker dialog and saving custom colors
C. Applying random colors automatically
D. Using pre-defined themes only

Q3. What type of form will be created to show time remaining for events?
A. Login form
B. Countdown timer form
C. Invoice entry form
D. Contact management form

Q4. Which previous lesson is especially important as a prerequisite for Developer Level 40?
A. Developer 39
B. Developer 16 (recordsets)
C. Expert 10
D. Beginner 1

Q5. Which version of Access are the lessons designed for?
A. Only Access 2003
B. Access 365 subscription or Access 2021, but should work back to 2007
C. Only Access for Mac
D. Only Access 2019

Q6. How will conditional formatting be applied to events in the countdown form?
A. Only by default settings
B. Based on how soon the event is coming up
C. Through table relationships
D. By exporting to Excel

Q7. What will lesson three teach about colors?
A. Only using color names in VBA
B. Creating a form to edit color schemes and using hex and RGB values
C. Printing color reports
D. Exporting color data to Word

Q8. Which VBA objects related to conditional formatting are studied in lesson four?
A. QueryDef and TableDef
B. FormatCondition and FormatConditions collection
C. Report and Controls
D. CommandBars

Q9. What is taught about placing forms on the screen?
A. How to center all forms
B. How to open a form to the right of another form
C. How to minimize forms by default
D. How to dock forms to the bottom

Q10. What is a user defined type (UDT) in VBA?
A. A type of database object
B. A custom data structure the user defines in VBA
C. A kind of form property
D. An Access table property

Q11. What dialog will be used to allow users to select fonts at runtime?
A. Windows printer dialog
B. Windows font dialog
C. Windows regional settings
D. Windows search dialog

Q12. What can be customized in the letter writer by applying knowledge from lesson seven?
A. Letter layout only
B. The font for each letter
C. Letter attachments
D. The logo image

Q13. What does the lesson say about posting questions regarding the class?
A. You must email the instructor
B. Post at the bottom of the course page and check existing answers first
C. Post only in Facebook groups
D. Write a letter to Microsoft

Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-B; 7-B; 8-B; 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 is Microsoft Access Developer Level 40, where we are focusing on controlling conditional formatting using VBA. Even though this might not seem revolutionary, by the end of this lesson you will be able to give your users complete control over color schemes within your database forms and reports. I will show you how to use the Windows Color Picker so users can choose their own colors and save them in your tables. In addition, we will cover how to use the Windows Font dialog, allowing custom font choices as well.

One of our projects in this class will be to create a countdown timer form. This will display how many days are left until certain events you have coming up. Through this, we can let users set their own fonts and color preferences for each event or scheme. There is quite a bit to cover in this class.

It is important that you have completed all of my previous Access classes - beginner, expert, advanced, and the earlier developer levels. These courses are designed to be followed in sequence, as many of the topics build on previous material. For example, this class relies a lot on using recordsets, which I discussed in depth in Developer Level 16, so make sure you are familiar with that before jumping in here.

For this series, I am using Microsoft Access as part of a Microsoft 365 subscription, which I highly recommend. This video was recorded in August 2022. If you are using the retail version, this will be similar to Access 2021. However, almost all the techniques we discuss here will be compatible with older versions as far back as Access 2007.

If you run into any questions about the material I present in this class, just scroll to the bottom of the lesson's page and post your question. Before you post, take a look through the questions that are already there, since it's possible someone else has already asked the same thing. You can also subscribe to get notified about new questions and answers related to this class. If your question is more general and not tied to today's lesson, head over to my Access forum and ask there.

Now let's break down exactly what you will learn in Access Developer Level 40.

In lesson one, we will build an event countdown form. This is useful for keeping track of upcoming events like conferences, beach trips, surgeries, or anything you want reminders for. The form will alert you about your events when the database loads. For example, it might notify you that you have a conference the next day or a day at the beach coming up in four days. I will show you how to set up this display form, introduce a bit of conditional formatting to highlight events happening soon, and add buttons for adding, editing, or deleting events using VBA. We will also create an event editor as a pop-up form and cover a lot of related techniques.

In lesson two, we will expand on the countdown form and add additional conditional formatting. The key feature here is letting users select their own color schemes; they can choose from options such as red, blue, black, green, and apply different themes to each event if they wish.

Lesson three focuses on building a custom color editor. I will show you how to create a form for users to edit their color themes, explain working with hex and RGB values, convert between these formats, and integrate the Windows Color dialog to let users pick colors and save their choices as part of their color schemes.

Lesson four will introduce conditional formatting with VBA. We will look at objects such as FormatCondition and the FormatConditions collection, and I will demonstrate how to add and remove them through code. There is a limitation on the maximum number of conditional formatting rules you can have, and I will show you how to check for this by counting records in the form. I will also show you techniques beyond just relying on DCount.

Lesson five continues with more conditional formatting through VBA. We will bring additional form controls into our conditional formatting routines, like number and background boxes. I will show you how to position a form relative to another, so for example you can have the countdown form always open to the right of your main menu, no matter where that is placed on the screen. We will also set up our forms to refresh in real time when updates are made from the color editor. Plus, I will review some improvements to the color picker and the RGB and hex conversion functions, and demonstrate these updates.

Moving on to lesson six, we will cover user defined types in VBA. I will explain what they are, how to define and use them, how to pass them around as variables, and talk about how they always use ByRef.

In lesson seven, the focus shifts to the Windows font dialog. This allows us to change fonts at runtime, letting users select their preferred font for events or countdown displays. You can also use this approach to enable font choices in reports. I will cover how to use the dialog, save user selections in a settings table, and load these preferences back into your forms when needed.

Finally, in lesson eight we will apply what we have learned about fonts to our letter writer feature. Here, users will be able to specify a different font for each letter, and these settings will be saved as part of each letter's record. I will discuss the unique challenges that can come up when using these custom fonts in reports, and how to address them.

You can watch the complete video tutorial, which includes step-by-step demonstrations for everything discussed here, on my website at the link below. Live long and prosper, my friends.

Topic List

Building an event countdown form
Conditional formatting on forms with VBA
Creating buttons to add, edit, and delete events
Making a pop-up form to edit events
Customizing form color schemes via user selection
Building a custom color editor form
Converting between hex and RGB color values
Using the Windows Color Picker dialog in Access
Saving user-selected colors to a table
Working with the FormatCondition object in VBA
Adding and deleting FormatConditions in VBA
Counting records on a form with VBA (without DCount)
Applying conditional formatting loops to multiple controls
Opening a form positioned relative to another form
Refreshing forms to display updated colors in real time
Enhancing the color picker with improved RGB and hex functions
Introduction to user defined types in VBA
Passing user defined types by reference in VBA
Using the Windows Font dialog at runtime
Saving and loading font selections in a settings table
Applying custom fonts to letters in a letter writer form

Article

In this tutorial, we will focus on changing conditional formatting in Microsoft Access using VBA. This technique allows your users to set up custom color schemes for different items on forms and reports. You will also learn how to use the Windows Color Picker dialog so users can pick their own colors, which we then save in a table for later use. In addition, we will explore the Windows Font dialog box so that users can pick custom fonts for their events or other display elements.

To make the concepts practical, we will build an event countdown form. Imagine you have a list of upcoming events - it could be conferences, vacations, appointments, or anything else. Each time you open your database, the countdown form reminds you how many days are left until each event. To make this information stand out, we will use conditional formatting. For example, if an event is coming up tomorrow, it could display in red; if it is in less than a week, it might show in yellow. This makes it easy to spot important events at a glance.

Let us start by creating a table to store the events. You might have fields like EventID (AutoNumber), EventName (Text), EventDate (Date/Time), ColorSchemeID (Number), and FontSettingsID (Number). This will let each event have its own custom color scheme and font, which we will also define in separate tables.

Next, create a form to display upcoming events. You can add controls for the event name, the days left (calculated as EventDate minus Date()), and use conditional formatting to highlight events based on how soon they are. While Access lets you set simple conditions directly, to let users pick their own colors you need VBA.

To let users choose a custom color, we will use the Windows Color Picker. You can trigger this dialog by putting a command button on your form. When the user clicks this button, a standard Windows color palette opens. After the user picks a color, the color value (usually as a Long integer or an RGB code) is saved in your color scheme table.

Here is some sample VBA code to open the Color Picker dialog and get the color value:

```vba
Private Declare Function ChooseColor Lib "comdlg32.dll" Alias "ChooseColorA" (pChoosecolor As CHOOSECOLOR) As Long
Private Type CHOOSECOLOR
lStructSize As Long
hwndOwner As Long
hInstance As Long
rgbResult As Long
lpCustColors As String
Flags As Long
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Public Function PickColor(Optional lngColor As Long = 0) As Long
Dim cc As CHOOSECOLOR
Dim CustColors(16) As Long
Dim lRet As Long
cc.lStructSize = Len(cc)
cc.hwndOwner = Application.hWndAccessApp
cc.rgbResult = lngColor
cc.Flags = 0
lRet = ChooseColor(cc)
If lRet <> 0 Then
PickColor = cc.rgbResult
Else
PickColor = lngColor
End If
End Function
```

To use this function, create a button that calls PickColor and store the result in your color scheme table.

Now, let us discuss conditional formatting using VBA. In Access, each control, like a text box, has a FormatConditions collection. Each item in the collection is a FormatCondition object, which specifies the format to use when a certain condition is true. With VBA, you can clear old formatting and add new ones based on your table settings.

Here is how to remove all conditional formats and then add a new format condition:

```vba
Dim fc As FormatCondition

' Remove all format conditions
With Me.txtDaysLeft.FormatConditions
Do While .Count > 0
.Delete 0
Loop
End With

' Add new format condition: if days left equals 1 (i.e., event is tomorrow), make background red
Set fc = Me.txtDaysLeft.FormatConditions.Add(acFieldValue, acEqual, 1)
fc.BackColor = RGB(255, 0, 0)
fc.ForeColor = RGB(255, 255, 255) ' White text

' Add another condition: less than 5 days, background yellow
Set fc = Me.txtDaysLeft.FormatConditions.Add(acFieldValue, acLessThan, 5)
fc.BackColor = RGB(255, 255, 0)
fc.ForeColor = RGB(0, 0, 0) ' Black text
```

You can expand this so users can choose the colors they want for each status and save them in a settings table.

To manage a custom color scheme, create a form where users can edit colors for different statuses (for example, "Upcoming soon", "Event today", "Normal"). Use the color picker code above to let them select the color, then save the RGB or Long value. Later, when your form loads, read these settings and apply them to your controls using the FormatConditions collection.

You can also let users pick fonts for display items. To do this, you use the Windows Font Dialog. The process is similar: you launch the font dialog using API calls, capture the user's choice, and save the font name, size, and other properties in your settings table. When your form or report loads, you apply these settings with VBA, changing the FontName and FontSize properties of controls.

Working with color and font settings stored in tables means each user, event, or letter can have a different look. For instance, in a letter writer feature, you could let users pick a unique font for each letter and store these selections in the letter records. When the user opens the letter, you read its font settings from the table and apply them directly to the controls on your form, and use similar logic when generating reports.

To sum up, making your Access applications customizable with user-defined conditional formatting and fonts involves three main steps. First, provide flexible storage by creating tables for color and font settings. Second, use Windows dialogs in VBA to let users choose colors and fonts. Third, apply these choices at runtime using VBA to set the appearance of your forms and reports. By combining these techniques, you give your users a much richer and more personal experience with your Access applications.