Catégorie : Excel VBA Course

Goal:

We want to apply dynamic conditional formatting using VBA, so that the format changes automatically based on cell values. This can be useful, for example, when you want to color code cells based on specific criteria like greater than, less than, between, etc.

Step-by-Step Guide:

1. Understanding Conditional Formatting in Excel VBA

Conditional formatting allows you to automatically format cells based on specific conditions (e.g., changing the cell color if the value exceeds a certain number). The VBA approach allows for dynamic application of these formats based on changing data.

2. Preparing the Worksheet

Let’s assume you have a range of data (e.g., A1:A10) and you want to apply conditional formatting to highlight the cells that meet specific criteria.

3. Writing the VBA Code

Below is a detailed VBA code to create dynamic conditional formatting for the range A1:A10. The code applies formatting based on the following conditions:

• Cells that are greater than 50 will be highlighted in green.
• Cells that are less than 20 will be highlighted in red.
• Cells that are between 20 and 50 will be highlighted in yellow.

VBA Code:

Sub CreateDynamicConditionalFormatting()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cf As FormatCondition
    ' Set the target worksheet and rang
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rng = ws.Range("A1:A10")
    ' Clear any existing conditional formatting
    rng.FormatConditions.Delete
    ' 1. Apply formatting for cells greater than 50
    Set cf = rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="50")
    cf.Interior.Color = RGB(0, 255, 0) ' Green color
    ' 2. Apply formatting for cells less than 20
    Set cf = rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="20")
    cf.Interior.Color = RGB(255, 0, 0) ' Red color
    ' 3. Apply formatting for cells between 20 and 50
    Set cf = rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="20", Formula2:="50")
    cf.Interior.Color = RGB(255, 255, 0) ' Yellow color
    MsgBox "Dynamic Conditional Formatting applied successfully!"
End Sub

Explanation of the Code

• Set ws and rng: We specify the worksheet (ws) and the range (rng) to apply the conditional formatting. In this case, we’re working with « Sheet1 » and the range A1:A10.
• Clear Existing Formatting: The line rng.FormatConditions.Delete ensures that any pre-existing conditional formatting on the range is cleared before applying new rules.
• Adding Format Conditions: For each condition (greater than, less than, and between), we use FormatConditions.Add. Here’s a breakdown of the method:
  • Type:=xlCellValue: We’re applying the condition to cell values.
  • Operator:=xlGreater, xlLess, xlBetween: Specifies the type of condition (greater than, less than, between).
  • Formula1 and Formula2: These are the values we compare against. For example, in the case of xlGreater, Formula1 is set to « 50 », meaning cells greater than 50 will be formatted.
• Formatting the Cells: The cf.Interior.Color = RGB(r, g, b) line sets the background color of the cells that meet the condition. In the example:
  • Green (0, 255, 0) for values greater than 50.
  • Red (255, 0, 0) for values less than 20.
  • Yellow (255, 255, 0) for values between 20 and 50.

4.Running the Code

To run the code:

• Open the workbook where you want to apply conditional formatting.
• Press Alt + F11 to open the VBA editor.
• Insert a new module: Insert > Module.
• Paste the code into the module.
• Press F5 or run the CreateDynamicConditionalFormatting macro from the « Run » menu.

5.Modifying for Dynamic Changes

You can adjust the conditions dynamically by linking them to cell values. For example, if you want the condition to depend on a value in a specific cell (say, B1), you can modify the formula as follows:

Set cf = rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:= »=B1″)

This way, the formatting will change based on the value in B1.

Conclusion

This code demonstrates how to apply dynamic conditional formatting to a range of cells using VBA in Excel. You can modify the conditions and apply more complex formatting as needed. The power of VBA allows for even more advanced logic, such as using formulas or applying different types of formatting (fonts, borders, etc.) based on dynamic criteria.

Creating dynamic filter criteria in Excel VBA allows you to automatically apply specific filter conditions to a range of data, based on user input or pre-defined rules. This can be helpful when you need to filter data dynamically without manually changing the criteria each time. Below is a detailed explanation and VBA code that demonstrates how to create dynamic filter criteria using VBA.

Objective:

• We want to create a VBA macro that applies dynamic filter criteria to an Excel table, based on user input or a specific range of cells.
• The filter criteria can vary depending on the values in these cells or predefined conditions (such as date ranges, numerical ranges, or text criteria).

Approach:

1. Identify the Range: First, we need to identify the data range that we want to apply the filter on.
2. User Input or Predefined Criteria: The filter criteria will be taken from either user input or predefined conditions stored in specific cells.
3. Apply the Filter: Using the AutoFilter method, we can apply the filter dynamically based on the specified criteria.

Example Scenario:

• We have a data table in Sheet1, and we want to apply a filter dynamically based on:
  • A date range (start date and end date) from cells A1 (start date) and A2 (end date).
  • A text filter for the « Category » column from cell B1.

VBA Code:

Sub ApplyDynamicFilter()
    ' Define variables
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim startDate As Dat
    Dim endDate As Date
    Dim category As String
    ' Set the worksheet and table range
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set tbl = ws.ListObjects("Table1") ' Assuming the table is named Table1
    ' Get the user-defined filter criteria
    startDate = ws.Range("A1").Value ' Start date from cell A1
    endDate = ws.Range("A2").Value ' End date from cell A2
    category = ws.Range("B1").Value ' Category from cell B1
    ' Remove any existing filters
    tbl.AutoFilter.ShowAllData   
    ' Apply the filter based on the dynamic criteria
    ' Filter by Date (assuming the date column is the 1st column)
    tbl.Range.AutoFilter Field:=1, Criteria1:=">=" & startDate, Operator:=xlAnd, Criteria2:="<=" & endDate  
    ' Filter by Category (assuming the Category column is the 2nd column)
    tbl.Range.AutoFilter Field:=2, Criteria1:=category
End Sub

Explanation:

1. Define Variables: We define ws for the worksheet and tbl for the table (ListObject). The filter criteria, such as startDate, endDate, and category, are assigned values from specific cells (A1, A2, and B1).
2. Set the Worksheet and Table:
   • The ws variable is assigned the worksheet Sheet1 where the data table exists.
   • The tbl variable represents the table (ListObject), and we assume it’s named Table1. You can replace this with your own table name or the correct reference.
3. Remove Existing Filters: The line tbl.AutoFilter.ShowAllData removes any previously applied filters so that the new filter criteria can be applied from scratch.
4. Apply Date Filter:
   • We apply the filter for the date range using the AutoFilter method.
   • The Field:=1 indicates the first column (in this case, the date column).
   • The Criteria1 is set to the start date (from cell A1), and Criteria2 is set to the end date (from cell A2), creating a date range filter.
   • The Operator:=xlAnd ensures that both criteria (start date and end date) are applied simultaneously.
5. Apply Text Filter for Category:
   • Similarly, we apply a text filter for the « Category » column (assumed to be the 2nd column in the table).
   • Criteria1:=category filters the rows where the category matches the value from cell B1.

Notes:

• Column Indexing: The Field parameter in AutoFilter refers to the column index (1-based). In the above example, the first column contains dates, and the second column contains categories. Adjust these values based on your actual table layout.
• Data Types: Make sure the data types in the filter criteria match the column types (e.g., dates should match a date format, text should match string criteria).
• Error Handling: It’s a good practice to add error handling to ensure that the data entered in the filter criteria cells is valid and the macro doesn’t fail unexpectedly.

Advanced Use Case:

If you want to create a more complex dynamic filter (e.g., based on multiple criteria across several columns or using dynamic ranges), you can modify the code by adding more conditions or using input dialogs for real-time user input.

This approach gives you flexibility in filtering data without having to manually adjust the filter criteria each time, making it ideal for repetitive tasks or data analysis automation.

VBA Code to Create a Dropdown List in Excel

This code will:

1. Define a range in a worksheet.
2. Populate it with a list of values.
3. Apply data validation to create a dropdown list in a target cell.

Sub CreateDropdownList()
    Dim ws As Worksheet
    Dim rng As Range
    Dim targetCell As Range
    Dim listRange As Range   
    ' Set worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Define the range where the dropdown options will be stored
    Set rng = ws.Range("A1:A5")   
    ' Populate the range with dropdown options
    rng.Value = WorksheetFunction.Transpose(Array("Option 1", "Option 2", "Option 3", "Option 4", "Option 5"))   
    ' Define the target cell where the dropdown list will be applied
    Set targetCell = ws.Range("C1")   
    ' Set up the validation list
    With targetCell.Validation
        ' Remove existing validation
        .Delete
        ' Add new validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & rng.Address
        ' Optional: Display an input message when the cell is selected
        .InputTitle = "Select an Option"
        .InputMessage = "Choose from the list"
        ' Optional: Customize error message
        .ErrorTitle = "Invalid Selection"
        .ErrorMessage = "Please select a valid option from the dropdown list."
        .ShowInput = True
        .ShowError = True
    End With  
    ' Notify the user
    MsgBox "Dropdown list created in " & targetCell.Address, vbInformation, "Success"
End Sub

Explanation

1. Define Worksheet and Ranges
   • The script works on Sheet1, but you can change the sheet name as needed.
   • rng (A1:A5) holds the dropdown values.
   • targetCell (C1) is where the dropdown will appear.
2. Populate the Dropdown List
   • The rng.Value = WorksheetFunction.Transpose(Array(…)) fills the list dynamically.
3. Apply Data Validation
   • .Delete removes any existing validation in the target cell.
   • .Add Type:=xlValidateList creates a dropdown list.
   • Formula1:= »= » & rng.Address links the list source.
   • .InputTitle and .InputMessage show hints when selecting the cell.
   • .ErrorTitle and .ErrorMessage display custom error messages.
4. Notify the User
   • MsgBox confirms that the dropdown list was successfully created.

Creating a drop-down calendar (or date picker) in Excel using VBA (Visual Basic for Applications) typically involves using a calendar control or a custom dialog box. While Excel does not provide a built-in calendar control in all versions, you can work around this by using a UserForm (a custom form) or other controls.

Step 1: Add a Calendar Control in a UserForm

• Open the VBA Editor:
  • Open Excel and press Alt + F11 to open the VBA editor.
• Create a New UserForm:
  • In the VBA editor, go to the Insert menu and select UserForm to create a new UserForm.
• Add a Calendar Control:
  • In the toolbox (if visible), look for the Microsoft Date and Time Picker Control or Microsoft Calendar Control. If these controls are unavailable (which may vary by Excel version), we will simulate a calendar using buttons or labels.

If the control is not visible, right-click on the toolbox, choose « Additional Controls, » and add the calendar control if available.

• Add a Button to Open the Calendar:
  • You can add a Button on your Excel sheet that will open the UserForm.

Step 2: Create a Button to Show the Calendar

Go back to your Excel sheet, then add a button with the following steps:

• Insert a Button:
  • Go to the Developer tab, then click Insert, and choose a button from the Form Controls.
  • Place the button on your sheet.
• Assign a Macro to the Button:
  • Right-click the button and choose « Assign Macro », then select « New » to create a macro.

Step 3: VBA Code to Show the Calendar

Here is an example VBA code to open a calendar when you click the button. This code uses a UserForm with a DatePicker control and shows the selected date in an Excel cell.

• Code for the UserForm: If you’ve added a DatePicker control to your UserForm, use this code:

' Code for the UserForm
Private Sub Calendar1_Click()
    ' Once a date is selected from the calendar, place it in the active cell
    ActiveCell.Value = Calendar1.Value
    ' Close the UserForm after selection
    Me.Hide
End Sub

• Code to Open the UserForm with the Calendar: This code will open the UserForm when you click the button in Excel.

Sub OpenCalendar()
    ' Show the UserForm containing the calendar
    UserForm1.Show
End Sub

Step 4: Using the UserForm

Now that you’ve created the UserForm and attached the macro to the button:

• Click on the button in Excel.
• The UserForm with the calendar will pop up.
• You select a date, and it will be automatically inserted into the active cell in the Excel sheet.

Option Without DatePicker Control (if not available)

If the DatePicker control is not available in your version of Excel, you can create a custom calendar using buttons and labels to display the days of the month. This is a bit more complex and involves using loops and events to update the calendar each month.

Code VBA for a Custom Calendar (without DatePicker)

Here’s an example of a simple calendar without using a DatePicker control, using buttons to represent the days of the month:

• Create a Calendar Using Buttons and Labels: You can generate a custom calendar using buttons that represent the days of the month. This is a little more involved, but it’s a way to simulate a calendar.

Private Sub UserForm_Initialize()
    ' Initialize the calendar
    Dim i As Integer
    Dim j As Integer
    Dim d As Date
    Dim startDay As Integer
    Dim lastDay As Integer
    Dim currentMonth As Integer
    Dim currentYear As Integer   
    currentMonth = Month(Date)
    currentYear = Year(Date)   
    ' First day of the month
    d = DateSerial(currentYear, currentMonth, 1)
    startDay = Weekday(d, vbSunday)
    ' Last day of the month
    lastDay = Day(DateSerial(currentYear, currentMonth + 1, 1) - 1)   
    ' Clear the old buttons
    For i = 1 To 42
        Me.Controls("Button" & i).Visible = False
    Next i   
    ' Fill buttons with days
    For i = 1 To lastDay
        Me.Controls("Button" & (startDay + i - 1)).Caption = i
        Me.Controls("Button" & (startDay + i - 1)).Visible = True
    Next i
End Sub
Private Sub CommandButton1_Click()
    ' Function to go to the previous month
    currentMonth = currentMonth - 1
    If currentMonth = 0 Then
        currentMonth = 12
        currentYear = currentYear - 1
    End If
    Call UserForm_Initialize
End Sub
Private Sub CommandButton2_Click()
    ' Function to go to the next month
    currentMonth = currentMonth + 1
    If currentMonth = 13 Then
        currentMonth = 1
        currentYear = currentYear + 1
    End If
    Call UserForm_Initialize
End Sub

Explanation:

• UserForm_Initialize: This procedure initializes the calendar, displaying the days of the current month. It uses Weekday to determine the first day of the month and then populates buttons with the days of the month.
• CommandButton1_Click: This moves the calendar to the previous month.
• CommandButton2_Click: This moves the calendar to the next month.

Conclusion

This approach shows you how to create a drop-down calendar in Excel using VBA. You can customize the calendar further based on your needs, for example, by adjusting the layout, adding buttons, or allowing the user to select a date from a dropdown list. If you face limitations with controls in your version of Excel, creating a custom calendar using buttons and labels can be a good alternative

Steps to Create a Donut Chart Using VBA in Excel:

Prepare the Data in Excel: Before running the VBA code, ensure that you have data structured in a table format. For example:

• Access the VBA Editor:
  • Open your Excel workbook.
  • Press Alt + F11 to open the VBA editor.
  • Click Insert, then select Module to create a new module.
• Copy the following VBA code into the module:

Sub CreateDonutChart()
    ' Declare variables
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim dataRange As Range   
    ' Define the worksheet and data range
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Replace "Sheet1" with your sheet name
    Set dataRange = ws.Range("A1:B5")  ' Replace "A1:B5" with the range of your data
    ' Create the donut chart
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225) ' Set position and size of the chart
    chartObj.Chart.SetSourceData Source:=dataRange  ' Set the data source for the chart
    ' Set the chart type to Donut
    chartObj.Chart.ChartType = xlDoughnut  ' Donut chart type is xlDoughnut
    ' Customize the chart (optional)
    With chartObj.Chart
        ' Add a chart title
        .HasTitle = True
        .ChartTitle.Text = "Category Distribution"       
        ' Change color of each segment
        .SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)  ' Red
        .SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = RGB(0, 255, 0)  ' Green
        .SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = RGB(0, 0, 255)  ' Blue
        .SeriesCollection(1).Points(4).Format.Fill.ForeColor.RGB = RGB(255, 255, 0)  ' Yellow       
        ' Display data labels (value and percentage)
        .ApplyDataLabels ShowValue:=True, ShowPercentage:=True      
        ' Optional: Add a legend
        .HasLegend = True
    End With
End Sub

Detailed Explanation of the Code:

• Variable Declarations:
  • ws: Represents the worksheet where the chart will be created.
  • chartObj: Represents the chart object that we will create.
  • dataRange: Represents the data range to be used for the chart.
• Defining the Worksheet and Data Range:
  • Set ws = ThisWorkbook.Sheets(« Sheet1 »): Defines the worksheet containing your data. Replace « Sheet1 » with your actual worksheet name.
  • Set dataRange = ws.Range(« A1:B5 »): Defines the range of data to be used for the chart. Change « A1:B5 » to the actual range of your data.
• Creating the Chart:
  • Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225): This creates a new chart object in the worksheet and sets its position (left, width, top, height) in pixels.
  • chartObj.Chart.SetSourceData Source:=dataRange: Sets the data source for the chart.
• Setting the Chart Type:
  • chartObj.Chart.ChartType = xlDoughnut: This changes the chart type to a donut chart (xlDoughnut).
• Customizing the Chart (Optional):
  • With chartObj.Chart: Opens a section to customize the chart.
  • .HasTitle = True: Adds a title to the chart.
  • .ChartTitle.Text = « Category Distribution »: Sets the title text of the chart.
  • .SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = RGB(255, 0, 0): Changes the color of the first segment to red (you can set other colors as well).
  • .ApplyDataLabels ShowValue:=True, ShowPercentage:=True: Displays data labels on the chart, showing both the values and percentages.
  • .HasLegend = True: Adds a legend to the chart.

Running the Code:

1. After pasting the code into the module, close the VBA editor.
2. Go back to Excel and press Alt + F8 to open the Macros window.
3. Select CreateDonutChart and click Run.

A donut chart will be created in the specified worksheet with the data you have defined.

Conclusion:

This VBA code creates a donut chart based on the defined data range and allows for various customizations, such as the chart title, segment colors, data labels, and legend. You can adjust the parameters and data range to suit your needs and adapt it for different scenarios.

We will use three ComboBox controls (for day, month, and year) to allow the user to select a date, and a Label to display the selected date.

Steps:

• Create a UserForm with three ComboBoxes and a Label to display the selected date.
• Populate the ComboBoxes with days, months, and years.
• Add code to display the selected date in a Label when the user selects a day, month, and year.

Complete VBA Code:

1. Creating the Form:

First, create a UserForm with:

• 3 ComboBox controls (for Day, Month, and Year).
• 1 Label control to display the selected date.

2. VBA Code in the UserForm Module:

' In the UserForm module
Private Sub UserForm_Initialize()
    ' Populate the Day ComboBox (1 to 31)
    Dim i As Integer
    For i = 1 To 31
        ComboBoxDay.AddItem i
    Next i   
    ' Populate the Month ComboBox (January to December)
    ComboBoxMonth.AddItem "January"
    ComboBoxMonth.AddItem "February"
    ComboBoxMonth.AddItem "March"
    ComboBoxMonth.AddItem "April"
    ComboBoxMonth.AddItem "May"
    ComboBoxMonth.AddItem "June"
    ComboBoxMonth.AddItem "July"
    ComboBoxMonth.AddItem "August"
    ComboBoxMonth.AddItem "September"
    ComboBoxMonth.AddItem "October"
    ComboBoxMonth.AddItem "November"
    ComboBoxMonth.AddItem "December"
    ' Populate the Year ComboBox (for example, from 2000 to 2024)
    Dim year As Integer
    For year = 2000 To 2024
        ComboBoxYear.AddItem year
    Next year
End Sub
Private Sub ComboBoxDay_Change()
    ' Update the displayed date whenever a day is selected
    DisplayDate
End Sub
Private Sub ComboBoxMonth_Change()
    ' Update the displayed date whenever a month is selected
    DisplayDate
End Sub
Private Sub ComboBoxYear_Change()
    ' Update the displayed date whenever a year is selected
    DisplayDate
End Sub
Private Sub DisplayDate()
    ' Check if all ComboBoxes have a selected value
    If ComboBoxDay.ListIndex <> -1 And ComboBoxMonth.ListIndex <> -1 And ComboBoxYear.ListIndex <> -1 Then
        ' Display the selected date in the Label
        LabelDate.Caption = ComboBoxDay.Value & " " & ComboBoxMonth.Value & " " & ComboBoxYear.Value
    End If
End Sub

Explanation:

• UserForm_Initialize:
  • This procedure is triggered when the form is initialized. It fills the three ComboBox controls with the days, months, and years.
  • For the days, it populates the ComboBox with values from 1 to 31.
  • For the months, it populates the ComboBox with the month names from January to December.
  • For the years, it populates the ComboBox with a range of years (from 2000 to 2024 in this case).
• ComboBoxDay_Change, ComboBoxMonth_Change, ComboBoxYear_Change:
  • These procedures are triggered when a user makes a selection in one of the ComboBox controls (day, month, or year).
  • Each time the user selects a day, month, or year, the DisplayDate function is called to update the displayed date in the Label.
• DisplayDate:
  • This procedure checks if all three ComboBoxes have a selected value (by checking the selected index of each ComboBox).
  • If all selections are valid, it displays the selected date in the Label control in the format « Day Month Year ».

User Interface:

• When the user opens the form, they see three ComboBox controls (for day, month, and year) along with a Label that will display the selected date.
• After the user selects a day, month, and year, the selected date is displayed in the Label (for example: « 15 February 2024 » if the user selects day 15, month February, and year 2024).

Example:

If the user selects:

• Day: 15
• Month: February
• Year: 2024

The Label will display:
« 15 February 2024 ».

Additional Suggestions:

• You can add further functionality, such as validating the date (for example, checking that February doesn’t have more than 29 days depending on the year).
• You can add buttons to validate or cancel the selection if needed.

Conclusion:

This code allows you to create a simple date selector using ComboBox controls in an Excel UserForm with VBA. You can customize it further for more specific needs, such as formatting the date or saving it to a cell in the workbook.