Catégorie : Excel VBA Course

A Thermometer Chart is a great way to visually represent progress towards a goal, such as tracking sales performance, project completion, or any percentage-based metric.

1. Understanding the Thermometer Chart Structure

A Thermometer Chart consists of:

• A background column representing 100% of the target.
• A filled column representing the actual progress.
• A properly formatted chart to resemble a thermometer.

The core concept behind this chart is using a Stacked Column Chart where:

• One data series represents the actual value.
• Another series represents the maximum possible value.

2. Preparing the Data for the Chart

We need a simple table with:

• Current Value (e.g., actual progress towards the goal).
• Target Value (e.g., 100%).

Metric	Value
Current Value	75
Target Value	100

3. VBA Code to Create a Dynamic Thermometer Chart

The following VBA macro automates the process of creating and formatting the Thermometer Chart:

Sub CreateThermometerChart()
     Dim ws As Worksheet
     Dim chartObj As ChartObject
     Dim chart As Chart
     Dim rng As Range
     ' Set the worksheet where the data is stored
     Set ws = ActiveSheet
     ' Define the data range for the chart
     Set rng = ws.Range("A1:B3")
 ' Assumes data starts at A1 with headers
     ' Delete any existing chart in the worksheet
     For Each chartObj In ws.ChartObjects
         chartObj.Delete
     Next chartObj
     ' Add a new chart
     Set chartObj = ws.ChartObjects.Add(Left:=100, Top:=50, Width:=300, Height:=400)
     Set chart = chartObj.Chart
     ' Set chart source data 
    chart.SetSourceData Source:=rng
     ' Change chart type to a Stacked Column Chart
     chart.ChartType = xlColumnStacke
     ' Format the chart to look like a thermometer
     With chart
         .HasTitle = True
         .ChartTitle.Text = "Thermometer Chart"
         .Legend.Delete ' Remove legend
         .Axes(xlCategory).Delete
 ' Remove horizontal axis 
  End With
' Format the data series
With chart.SeriesCollection(1)
.IsFiltered = False
.Format.Fill.ForeColor.RGB = RGB(255, 0, 0) ' Red color for the progress
.Format.Fill.Transparency = 0
End With
' Format the second series (background)
With chart.SeriesCollection(2)
.Format.Fill.ForeColor.RGB = RGB(200, 200, 200) ' Gray background
.Format.Fill.Transparency = 0.5 ' Slight transparency
End With
' Adjust the Y-Axis
With chart.Axes(xlValue)
.MaximumScale = ws.Range("B3").Value ' Set max scale to the target value
.MinimumScale = 0 ' Ensure the scale starts at zero
.TickLabels.NumberFormat = "0%"
End With
' Align the chart properly
chartObj.Left = ws.Range("D1").Left
chartObj.Top = ws.Range("D1").Top
End Sub

' Format the data series
With chart.SeriesCollection(1)
.IsFiltered = False
.Format.Fill.ForeColor.RGB = RGB(255, 0, 0) ' Red color for the progress
.Format.Fill.Transparency = 0
End With
' Format the second series (background)
With chart.SeriesCollection(2)
.Format.Fill.ForeColor.RGB = RGB(200, 200, 200) ' Gray background
.Format.Fill.Transparency = 0.5 ' Slight transparency
End With
' Adjust the Y-Axis
With chart.Axes(xlValue)
.MaximumScale = ws.Range("B3").Value ' Set max scale to the target value
.MinimumScale = 0 ' Ensure the scale starts at zero
.TickLabels.NumberFormat = "0%"
End With
' Align the chart properly
chartObj.Left = ws.Range("D1").Left
chartObj.Top = ws.Range("D1").Top
End Sub

4. Explanation of the Code

Step 1: Setting Up the Worksheet and Data

• The macro works on the active sheet.
• It assumes that column A contains labels and column B contains numerical values (Current & Target).

Step 2: Deleting Existing Charts

• Before adding a new chart, any existing charts in the sheet are deleted to prevent duplication.

Step 3: Creating a New Chart

• A ChartObject is inserted into the worksheet at a specified location.
• The source data for the chart is set using chart.SetSourceData.

Step 4: Changing Chart Type

• The macro sets the chart type to Stacked Column Chart (xlColumnStacked).

Step 5: Formatting the Thermometer Effect

• The first data series (Actual Value) is colored red to represent the progress.
• The second data series (Target Value) is colored gray to represent the full scale.
• The legend is removed for a cleaner look.
• The horizontal axis is deleted to give a thermometer appearance.

Step 6: Adjusting the Y-Axis

• The maximum scale is set dynamically to the Target Value.
• The axis labels are formatted as percentages.

5. Running the VBA Code

To run the macro:

• Open Excel and press ALT + F11 to open the VBA Editor.
• Insert a new Module (Insert → Module).
• Copy and paste the VBA code into the module.
• Run the macro by pressing F5.

6. Enhancements and Customizations
7. Make the Chart Update Automatically

• Instead of recreating the chart every time, modify the macro to update an existing chart when values change.

8. Add a UserForm for Input

• Allow users to enter values in a UserForm and dynamically update the chart.

9. Improve Aesthetics

• Add rounded edges and a glossy effect to the thermometer.
• Use gradient fills to enhance the visualization.

10. Conclusion

This VBA macro provides a structured way to create a dynamic Thermometer Chart in Excel. By following this guide, you can automate the visualization of progress tracking, making reports more interactive and insightful.

A Treemap Chart is a data visualization tool that represents hierarchical data as nested rectangles. Each category is assigned a rectangle whose size is proportional to the corresponding value.

1. Understanding Treemap Charts

• Treemaps are useful for displaying proportions within a hierarchy.
• They work well with structured data such as product sales by category and sub-category.

2. Setting Up the Data

To create a Treemap Chart using VBA, your data should be structured hierarchically, like this:

Category	Sub-Category	Value
Fruits	Apples	100
Fruits	Bananas	150
Fruits	Oranges	120
Vegetables	Carrots	80
Vegetables	Broccoli	90
Dairy	Milk	200
Dairy	Cheese	160

3. VBA Code to Create Treemap Chart

The following VBA macro will:

1. Insert a Treemap Chart in the active worksheet.
2. Format the chart for better readability.

VBA Code

Sub CreateTreemapChart()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim rng As Range
    Dim treemapChart As Chart
    ' Set worksheet
    Set ws = ActiveShee
    ' Define the data range (adjust as necessary)
    Set rng = ws.Range("A1:C8") ' A1:C8 contains Category, Sub-Category, and Value
    ' Insert Chart Object
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=400, Top:=50, Height:=300)
    ' Reference the Chart inside the ChartObject
    Set treemapChart = chartObj.Chart
    ' Set chart source data
    treemapChart.SetSourceData Source:=rng
    ' Change chart type to Treemap
    treemapChart.ChartType = xlTreemap
    ' Format Chart Title
    treemapChart.HasTitle = True
    treemapChart.ChartTitle.Text = "Sales Distribution by Category"
    ' Set legend position
    treemapChart.Legend.Position = xlLegendPositionBottom
    ' Improve readability of the chart
    With treemapChart
        .ApplyLayout (1) ' Apply a default layout
        .ChartStyle = 5 ' Use a pre-defined chart style
    End With
    ' Auto-size chart for better visibility
    chartObj.Width = 500
    chartObj.Height = 350
    chartObj.Top = 20
    chartObj.Left = 50
    ' Release objects
    Set treemapChart = Nothing
    Set chartObj = Nothing
    Set ws = Nothing
    Set rng = Nothing
    MsgBox "Treemap Chart Created Successfully!", vbInformation, "Success"
End Sub

Explanation of the VBA Code

Step 1: Selecting the Worksheet

Set ws = ActiveSheet

• This ensures the macro runs on the currently active worksheet.

Step 2: Defining the Data Range

Set rng = ws.Range("A1:C8")

• This specifies the range of data for the Treemap Chart.

Step 3: Inserting a Chart Object

Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=400, Top:=50, Height:=300)

• Adds a new chart to the worksheet with specific dimensions.

Step 4: Setting Up the Chart

Set treemapChart = chartObj.Chart
treemapChart.SetSourceData Source:=rng
treemapChart.ChartType = xlTreemap

• Defines the data source and sets the chart type to Treemap.

Step 5: Formatting the Chart

treemapChart.HasTitle = True
treemapChart.ChartTitle.Text = "Sales Distribution by Category"

• Enables and customizes the chart title.

treemapChart.Legend.Position = xlLegendPositionBottom

• Moves the legend to the bottom for clarity.

With treemapChart
    .ApplyLayout (1) ' Apply default layout
    .ChartStyle = 5 ' Use a pre-defined chart style
End With

• Applies formatting to enhance the appearance of the chart.

Step 6: Adjusting Chart Size and Position

chartObj.Width = 500
chartObj.Height = 350
chartObj.Top = 20
chartObj.Left = 50

• Resizes and repositions the chart on the worksheet.

Step 7: Cleaning Up Objects

Set treemapChart = Nothing
Set chartObj = Nothing
Set ws = Nothing
Set rng = Nothing

• Releases object references to free up memory.

5. Running the VBA Code

1. Open Excel and press ALT + F11 to open the VBA Editor.
2. Insert a new module and paste the VBA code.
3. Select your sheet and ensure data is structured correctly.
4. Run the macro CreateTreemapChart by pressing F5.

6. Expected Output

After running the macro, a Treemap Chart will appear on the active worksheet, displaying the hierarchical sales data. The rectangles will be sized proportionally to their values, making it easy to compare categories and sub-categories.

7. Additional Customization

You can enhance the chart by:

• Changing the chart title dynamically based on a cell value:

• ChartTitle.Text = ws.Range(« E1 »).Value

• Modifying color themes:

• ChartStyle = 10

• Adding Data Labels:

• ApplyDataLabels xlDataLabelsShowValue

• Adjusting the legend format:

• Legend.Font.Size = 12

8. Conclusion

This VBA macro automates the creation of a Treemap Chart in Excel, making it easy to visualize hierarchical data. You can modify the macro further to dynamically select data or enhance the chart formatting.

Stock Chart is used to represent stock prices or any data that includes open, high, low, and close values over a set period. In Excel, we can create a stock chart using VBA by arranging the data appropriately and then using Excel’s built-in charting features to create the chart.

Step-by-Step Explanation:

1. Data Preparation

   The data for a stock chart typically requires 4 columns:

   – Date (or Time Period)

   – Open Price

   – High Price

   – Low Price

   – Close Price

   The data must be structured properly, with each row representing one day of stock data.

2. Creating the Chart

   Excel has a built-in « Stock » chart type, which you can access through VBA. The stock chart supports several variations (Open-High-Low-Close, Volume-High-Low-Close, etc.), but we will focus on the Open-High-Low-Close version in this example.

3. VBA Code to Create the Stock Chart

Here’s a VBA code that demonstrates how to create a Stock Chart in Excel:

Sub CreateStockChart()
    Dim ws As Worksheet
    Dim rng As Range
    Dim chartObj As ChartObject
    ' Set reference to the current active sheet
    Set ws = ActiveSheet   
    ' Define the range containing the data
    ' Assuming the data is in columns A to E, with headers in row 1 and data starting from row 2
    Set rng = ws.Range("A1:E10") ' Adjust the range to your actual data   
    ' Create a new chart object
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)   
    ' Set the chart data source
    chartObj.Chart.SetSourceData Source:=rng   
    ' Set the chart type to Stock Chart (OHLC)
    chartObj.Chart.ChartType = xlStockOHLC   
    ' Set chart title
    chartObj.Chart.HasTitle = True
    chartObj.Chart.ChartTitle.Text = "Stock Price Chart"   
    ' Set axis titles
    chartObj.Chart.Axes(xlCategory, xlPrimary).HasTitle = True
    chartObj.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Date"   
    chartObj.Chart.Axes(xlValue, xlPrimary).HasTitle = True
    chartObj.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Price"   
    ' Format the chart (optional)
    With chartObj.Chart
        .Axes(xlCategory).CategoryNames = ws.Range("A2:A10") ' Set category names to dates
        .Axes(xlValue).MinimumScale = 0 ' Set minimum value for price axis
        .Axes(xlValue).MaximumScale = 100 ' Adjust as needed
    End With   
End Sub

 Explanation of the Code:

– Setting the Range:

  The code assumes that the data for the stock chart is in columns A to E, where column A contains the Date, and columns B, C, D, and E contain Open, High, Low, and Close values, respectively. You can adjust the range `ws.Range(« A1:E10 »)` to match your actual data.

– Creating the Chart:

  The `ws.ChartObjects.Add` method is used to create a new chart on the active sheet. The `Left`, `Width`, `Top`, and `Height` parameters specify the position and size of the chart.

– Setting the Chart Type:

  `chartObj.Chart.ChartType = xlStockOHLC` tells Excel to use the OHLC (Open-High-Low-Close) version of the stock chart.

– Chart Title and Axis Titles:

  Titles for the chart and axes are added using the `Chart.HasTitle` and `Axes.HasTitle` properties. You can modify the text for the titles according to your preference.

– Formatting the Axes:

  The `CategoryNames` property of the x-axis is set to the dates from column A (`ws.Range(« A2:A10 »)`). Additionally, you can adjust the minimum and maximum scale of the y-axis for better visualization of stock prices.

Customization:

– Data Range: You can adjust the range `A1:E10` to include more rows of stock data. Ensure the data is in the correct format.

– Chart Type: You can change the chart type to other stock variations (e.g., Volume-High-Low-Close) by setting `ChartType = xlStockVHLC`.

– Formatting: You can customize the chart appearance further, such as changing the colors of the price bars, adding gridlines, or adjusting the axis formatting.

How to Run the Code:

1. Open Excel and press `Alt + F11` to open the VBA editor.
2. Insert a new module by clicking `Insert > Module`.
3. Paste the above code into the module.
4. Press `F5` to run the macro or assign it to a button in your workbook.

This code will generate a stock chart based on the data you provide and display it in your worksheet.

1. steps to Create a Tree Map Chart with VBA

To create a Tree Map Chart using VBA, we will:

• Prepare sample hierarchical data in an Excel worksheet.
• Insert a Tree Map Chart.
• Format the chart for better readability.

2. VBA Code to Create a Tree Map Chart

The following VBA code:

• Inserts sample data in an Excel worksheet.
• Creates a Tree Map Chart.
• Adjusts formatting.

Sub CreateTreeMapChart()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim treeMapChart As Chart
    Dim dataRange As Range
    ' Step 1: Set the worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets("TreeMapData")
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Sheets.Add
        ws.Name = "TreeMapData"
    End If
    On Error GoTo 0
    ' Step 2: Add Sample Data for Tree Map
    ws.Cells.Clear
    ws.Range("A1:C1").Value = Array("Category", "Subcategory", "Value"
    ws.Range("A2:C10").Value = Array( _
        Array("Fruits", "Apples", 50), _
        Array("Fruits", "Bananas", 30), _
        Array("Fruits", "Oranges", 40), _
        Array("Vegetables", "Carrots", 20), _
        Array("Vegetables", "Potatoes", 35), _
        Array("Vegetables", "Tomatoes", 25), _
        Array("Dairy", "Milk", 60), _
        Array("Dairy", "Cheese", 45), _
        Array("Dairy", "Yogurt", 30)
    ' Step 3: Define the data range
    Set dataRange = ws.Range("A1:C10")
    ' Step 4: Create the Tree Map Chart
    Set chartObj = ws.ChartObjects.Add(Left:=100, Top:=50, Width:=400, Height:=300)
    Set treeMapChart = chartObj.Chart
    treeMapChart.SetSourceData Source:=dataRange
    treeMapChart.ChartType = xlTreemap
    ' Step 5: Format the Tree Map Chart
    With treeMapChart
        .HasTitle = True
        .ChartTitle.Text = "Tree Map Chart - Sales Data"
        .ChartTitle.Font.Size = 14
        .ChartTitle.Font.Bold = True
        .Legend.Position = xlBottom
    End With
    ' Step 6: Autofit columns
    ws.Columns("A:C").AutoFit
    ' Notify user
    MsgBox "Tree Map Chart created successfully!", vbInformation, "Success"
End Sub

Explanation of the Code

Step 1: Define the Worksheet

Set ws = ThisWorkbook.Sheets("TreeMapData")
If ws Is Nothing Then
    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "TreeMapData"
End If

• The code checks if a worksheet named « TreeMapData » exists.
• If not, it creates a new worksheet and assigns it this name.

Step 2: Insert Sample Data

ws.Range("A1:C1").Value = Array("Category", "Subcategory", "Value")
ws.Range("A2:C10").Value = Array( _
    Array("Fruits", "Apples", 50), _
    Array("Fruits", "Bananas", 30), _
    Array("Fruits", "Oranges", 40), _
    Array("Vegetables", "Carrots", 20), _
    Array("Vegetables", "Potatoes", 35), _
    Array("Vegetables", "Tomatoes", 25), _
    Array("Dairy", "Milk", 60), _
    Array("Dairy", "Cheese", 45), _
    Array("Dairy", "Yogurt", 30))

• The headers « Category », « Subcategory », and « Value » are set.
• Sample hierarchical data is inserted.

Step 3: Define the Data Range

Set dataRange = ws.Range("A1:C10")

• The data range for the chart is defined.

Step 4: Create and Insert the Tree Map Chart

Set chartObj = ws.ChartObjects.Add(Left:=100, Top:=50, Width:=400, Height:=300)
Set treeMapChart = chartObj.Chart
treeMapChart.SetSourceData Source:=dataRange
treeMapChart.ChartType = xlTreemap

• A chart is inserted into the worksheet.
• The data is assigned to the chart.
• The chart type is set to Tree Map (xlTreemap).

Step 5: Format the Tree Map Chart

With treeMapChart
    .HasTitle = True
    .ChartTitle.Text = "Tree Map Chart - Sales Data"
    .ChartTitle.Font.Size = 14
    .ChartTitle.Font.Bold = True
    .Legend.Position = xlBottom
End With

• A title is added to the chart.
• The title’s font size and bold property are set.
• The legend is positioned at the bottom.

Step 6: Autofit Columns and Display Success Message

ws.Columns("A:C").AutoFit
MsgBox "Tree Map Chart created successfully!", vbInformation, "Success"

• Columns are resized for better visibility.
• A message box informs the user that the Tree Map Chart is created successfully.

4. Running the VBA Code

• Open Excel and press ALT + F11 to open the VBA Editor.
• Insert a New Module.
• Copy and paste the VBA code.
• Run CreateTreeMapChart.

5. Expected Output

• A new worksheet named TreeMapData is created.
• The sample data is added.
• A Tree Map Chart is inserted and formatted.

6. Customization Options

You can modify:

• The data to fit your needs.
• The chart title (treeMapChart.ChartTitle.Text).
• The chart size (Width, Height).
• The chart position (Left, Top).

7. Conclusion

This VBA macro automates the process of creating a Tree Map Chart in Excel, saving time and ensuring consistency.

To create tooltips in a UserForm using Excel VBA, you can use the ControlTipText property for the form’s controls (like buttons, text boxes, combo boxes, etc.). This allows you to display a short description when a user hovers over a control, providing helpful hints or additional information.

Step-by-Step Process to Create Tooltips in UserForm

1. Design the UserForm:
   • Open the Visual Basic for Applications (VBA) editor in Excel (Alt + F11).
   • Create a new UserForm by clicking Insert > UserForm.
   • Add controls (such as TextBoxes, Buttons, ComboBoxes) to the UserForm.
2. Set Tooltips Using ControlTipText Property:
   • Each control in a UserForm has a ControlTipText property where you can specify the text for the tooltip.
   • This tooltip will appear when the user hovers over the control.

Example Code for Adding Tooltips to Controls

Private Sub UserForm_Initialize()
    ' Setting Tooltips for Controls
    ' Tooltip for a TextBox
    TextBox1.ControlTipText = "Enter your name here."
    ' Tooltip for a Button
    CommandButton1.ControlTipText = "Click to submit the form.
    ' Tooltip for a ComboBox
    ComboBox1.ControlTipText = "Select an option from the dropdown list."
    ' Tooltip for a CheckBox
    CheckBox1.ControlTipText = "Tick this box if you agree to the terms."
    ' Tooltip for a Label
    Label1.ControlTipText = "This label displays the instructions."
End Sub

Explanation of Code:

• UserForm_Initialize: This event runs when the UserForm is initialized. It’s used to set up the initial properties of the controls on the form.
• ControlTipText Property:
  • The ControlTipText property holds the text that will appear as the tooltip.
  • In this example, TextBox1.ControlTipText is set to display the message « Enter your name here » when the user hovers over TextBox1.
  • You can do the same for other controls like buttons, combo boxes, checkboxes, etc.

Advanced Example with Dynamic Tooltips

If you want more dynamic control over the tooltips, you can change the tooltip text based on conditions, such as the selection in a ComboBox or the value entered in a TextBox.

For example, if you want to show a different tooltip based on what the user selects in a ComboBox:

Private Sub ComboBox1_Change()
    If ComboBox1.Value = "Option 1" Then
        ComboBox1.ControlTipText = "You selected Option 1."
    ElseIf ComboBox1.Value = "Option 2" Then
        ComboBox1.ControlTipText = "You selected Option 2."
    Else
        ComboBox1.ControlTipText = "Please select an option."
    End If
End Sub

Enhancing Tooltips with ToolTip-like Behavior

Excel VBA doesn’t directly support complex tooltips like those in web pages (e.g., with different colors, fonts, etc.), but you can simulate this behavior by creating a custom tooltip form.

Example: Creating a Custom Tooltip Form

Dim TooltipForm As Object
Private Sub UserForm_Initialize()
    ' Create a new TooltipForm instance
    Set TooltipForm = New UserForm
    TooltipForm.Visible = False  ' Hide it initially
End Sub

Private Sub TextBox1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    ShowTooltip "Enter your name here.", TextBox1
End Sub

Private Sub ShowTooltip(TooltipText As String, Control As Object)
    ' Position the TooltipForm near the control
    TooltipForm.Caption = TooltipText
    TooltipForm.Top = Control.Top + Control.Height + 5
    TooltipForm.Left = Control.Left
    TooltipForm.Visible = True
End Sub

Explanation of Custom Tooltip Code:

• TooltipForm: This is a new UserForm that will act as a custom tooltip. It is initially hidden.
• TextBox1_MouseMove: This event triggers when the user moves the mouse over TextBox1. It calls the ShowTooltip subroutine to display the custom tooltip.
• ShowTooltip: This procedure positions the TooltipForm relative to the control (in this case, TextBox1) and shows it with the desired tooltip text.

Considerations:

• Visibility: The tooltip should be visible only when necessary. For a custom tooltip, you should hide it when the user moves the mouse away from the control.
• Performance: While the ControlTipText property is simple and works well for most cases, using a custom tooltip form can be more flexible but may involve additional code to hide/show the tooltip at the right times.       

A Timeline Chart is a visual representation of events over time, often used for project management, historical data analysis, or tracking milestones. In Excel, you can create a Timeline Chart using a Scatter Plot with data labels.

Steps to Create a Timeline Chart Using VBA

1. Prepare Data: The timeline consists of two columns: Dates (X-axis) and Events (Y-axis).
2. Insert a Scatter Chart: Use VBA to create a scatter plot.
3. Format the Chart: Adjust markers, add labels, and set the axes properly.
4. Enhance Visualization: Customize colors, gridlines, and labels.

VBA Code for Timeline Chart

Below is the complete VBA code to generate a Timeline Chart dynamically.

Sub CreateTimelineChart()
    Dim ws As Worksheet
    Dim ch As ChartObject
    Dim rngX As Range, rngY As Range
    Dim lastRow As Long
    ' Define the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
    ' Find the last row with data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
     ' Define the data range (Dates in column A, Events in column B)
    Set rngX = ws.Range("A2:A" & lastRow)
    Set rngY = ws.Range("B2:B" & lastRow)
    ' Delete any existing chart
    For Each ch In ws.ChartObjects
        ch.Delete
    Next ch
    ' Add a new chart
    Set ch = ws.ChartObjects.Add(Left:=100, Width:=600, Top:=50, Height:=40
    ' Set chart type to Scatter Plot
    With ch.Chart
        .ChartType = xlXYScatter
        .SetSourceData Source:=Union(rngX, rngY)
        ' Format axes 
        With .Axes(xlCategory)
            .HasTitle = True
            .AxisTitle.Text = "Date"
            .TickLabels.Orientation = 45 ' Rotate labels for better readability
        End With
        With .Axes(xlValue)
            .HasTitle = True
            .AxisTitle.Text = "Events"
            .MajorGridlines.Delete ' Remove gridlines for clarity 
        End With
 ' Add Data Labels
        Dim i As Integer
        For i = 1 To .SeriesCollection(1).Points.Count
            With .SeriesCollection(1).Points(i)
                .ApplyDataLabels xlDataLabelsShowValue
            End With
        Next i         ' Customize chart appearance
        .HasTitle = True
        .ChartTitle.Text = "Project Timeline"
        .Legend.Delete
    End With
    ' Clean up
    Set ws = Nothing
    Set ch = Nothing
    Set rngX = Nothing
    Set rngY = Nothing
End Sub

Detailed Explanation of VBA Code

1. Identify the Worksheet and Data Range

Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name

• Defines the worksheet where the data is stored.

lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

• Finds the last row in column A to dynamically adjust the data range.

Set rngX = ws.Range("A2:A" & lastRow)
Set rngY = ws.Range("B2:B" & lastRow)

• Sets up the X-axis (dates) and Y-axis (events) range.

2. Remove Any Existing Chart

For Each ch In ws.ChartObjects
ch.Delete
Next ch

• Deletes any existing chart on the worksheet to prevent duplicates.

3. Insert a New Chart

Set ch = ws.ChartObjects.Add(Left:=100, Width:=600, Top:=50, Height:=400)

• Creates a new chart at a specified position.

.ChartType = xlXYScatter
.SetSourceData Source:=Union(rngX, rngY)

• Sets the chart type to Scatter Plot and assigns the data source.

4. Format Axes

With .Axes(xlCategory)
    .HasTitle = True
    .AxisTitle.Text = "Date"
    .TickLabels.Orientation = 45 ' Rotate labels
End With

• Labels the X-axis as « Date » and rotates labels for better readability.

With .Axes(xlValue)
    .HasTitle = True
    .AxisTitle.Text = "Events"
    .MajorGridlines.Delete ' Remove gridlines
End With

• Labels the Y-axis as « Events » and removes major gridlines.

5. Add Data Labels

Dim i As Integer
For i = 1 To .SeriesCollection(1).Points.Count
    With .SeriesCollection(1).Points(i)
        .ApplyDataLabels xlDataLabelsShowValue
    End With
Next i

• Loops through each data point and adds labels to display event names.

6. Customize Chart Appearance

.HasTitle = True
.ChartTitle.Text = "Project Timeline"
.Legend.Delete

• Sets the chart title as « Project Timeline » and removes the legend.

How to Use the VBA Code

1. Enter Data in Sheet1 (or change the sheet name in the code):

2. | A (Date)    |   B (Event)   |
3. |—————|————–|
4. | 01/01/2024  |  Project Start  |
5. | 15/02/2024  |  Phase 1 Done  |
6. | 10/04/2024  |  Testing Begins  |
7. | 20/06/2024  |  Final Review  |

1. Open the VBA Editor (ALT + F11).
2. Insert a New Module (Right-click on a module > Insert > Module).
3. Paste the VBA Code and run CreateTimelineChart.

Customizations

• Change Colors: Modify the marker styles using .MarkerStyle and .MarkerBackgroundColor.
• Event Labels: Use DataLabels.Position = xlLabelPositionAbove for better positioning.
• Dynamic Sheet Selection: Add an InputBox to let users select the sheet.

Conclusion

This VBA code efficiently generates a Timeline Chart in Excel. It dynamically reads dates and events, creates a scatter plot, and formats the chart for better readability

Features of Our Custom Time Picker

• Hours, Minutes, and AM/PM selection
• Spin buttons to increase/decrease hours and minutes
• Dropdown list for selecting AM or PM
• OK and Cancel buttons to confirm or close the picker
• Time input validation

Step-by-Step Implementation

1. Designing the UserForm

We will create a UserForm named TimePickerForm with the following controls:

Control Type	Name	Purpose
Label	lblHours	Displays « Hours » text
TextBox	txtHours	Displays selected hour
SpinButton	spnHours	Increases/Decreases hour
Label	lblMinutes	Displays « Minutes » text
TextBox	txtMinutes	Displays selected minute
SpinButton	spnMinutes	Increases/Decreases minute
Label	lblAMPM	Displays « AM/PM » text
ComboBox	cmbAMPM	Allows selection of AM/PM
Button	btnOK	Confirms the selected time
Button	btnCancel	Cancels and closes the form

2. Writing the VBA Code

Here is the detailed VBA code to handle the Time Picker functionality.

VBA Code for the UserForm:

Option Explicit

' Declare a variable to store the selected time
Public SelectedTime As String
' Initialize the Time Picker when the form loads
Private Sub UserForm_Initialize()
    ' Set default time to 12:00 AM
    txtHours.Text = "12"
    txtMinutes.Text = "00"
    ' Populate the AM/PM dropdown
    cmbAMPM.AddItem "AM"
    cmbAMPM.AddItem "PM"
    cmbAMPM.ListIndex = 0 ' Default to AM
    ' Set spin button properties
    spnHours.Min = 1
    spnHours.Max = 12
    spnHours.Value = 12
    spnMinutes.Min = 0
    spnMinutes.Max = 59
    spnMinutes.Value = 0
End Sub
' Handle Hour Spin Button
Private Sub spnHours_Change()
    txtHours.Text = Format(spnHours.Value, "00")
End Sub
' Handle Minute Spin Button
Private Sub spnMinutes_Change()
    txtMinutes.Text = Format(spnMinutes.Value, "00")
End Sub
' Handle Manual Input in Hours TextBox
Private Sub txtHours_Change()
    Dim h As Integer
    If IsNumeric(txtHours.Text) Then
        h = CInt(txtHours.Text)
        If h >= 1 And h <= 12 Then
            spnHours.Value = h
        Else
            txtHours.Text = "12" ' Reset invalid input
        End If
    Else
        txtHours.Text = "12"
    End If
End Sub
' Handle Manual Input in Minutes TextBox
Private Sub txtMinutes_Change()
    Dim m As Integer
    If IsNumeric(txtMinutes.Text) Then
        m = CInt(txtMinutes.Text)
        If m >= 0 And m <= 59 Then
            spnMinutes.Value = m
        Else
            txtMinutes.Text = "00" ' Reset invalid input
        End If
    Else
        txtMinutes.Text = "00"
    End If
End Sub
' Handle OK Button Click - Store the selected time
Private Sub btnOK_Click()
    SelectedTime = txtHours.Text & ":" & txtMinutes.Text & " " & cmbAMPM.Text
    Me.Hide ' Hide the form instead of unloading it
End Sub
' Handle Cancel Button Click - Close the form without saving
Private Sub btnCancel_Click()
    SelectedTime = "" ' Reset time
    Me.Hide
End Sub

3. Using the Time Picker in Your VBA Code

To display the Time Picker and get the selected time, use the following macro in a module:

Sub ShowTimePicker()
    Dim TimePicker As New TimePickerForm
    ' Show the UserForm
    TimePicker.Show vbModal
    ' Retrieve the selected time
    If TimePicker.SelectedTime <> "" Then
        MsgBox "You selected: " & TimePicker.SelectedTime, vbInformation, "Time Picker"
    Else
        MsgBox "Time selection canceled.", vbExclamation, "Time Picker"
    End If
End Sub

4. How It Works

1. The UserForm_Initialize event sets the default values (12:00 AM).
2. The spin buttons adjust hours (spnHours_Change) and minutes (spnMinutes_Change).
3. Manual input in textboxes is validated (txtHours_Change and txtMinutes_Change).
4. Clicking « OK » saves the selected time and hides the form.
5. Clicking « Cancel » hides the form without saving.
6. The ShowTimePicker macro launches the time picker and displays the selected time.

Enhancements

• You can format the selected time as a 24-hour format.
• You can integrate it with a date picker to get both date and time.
• You can add a preview label that shows the selected time dynamically.

Final Thoughts

This custom Time Picker provides an interactive and user-friendly way to select time values in Excel. Since Excel VBA lacks a built-in Time Picker control, this method offers a flexible alternative that ensures accuracy and ease of use.

Step 1: Insert a UserForm

1. Open Excel and press ALT + F11 to open the VBA Editor.
2. In the VBA Editor, go to Insert > UserForm. A new blank UserForm will appear.
3. Rename the UserForm for clarity. In the Properties Window, change the Name property of the UserForm to ufTabExample.

Step 2: Add a Tab Control

1. In the Toolbox, find the « MultiPage » control (which functions as a Tab Control).
2. If the Toolbox is not visible, press CTRL + T or go to View > Toolbox.
3. Click on the MultiPage control (it looks like multiple tabs) and draw it on the UserForm.
4. Rename the MultiPage control for clarity:
   • Select the MultiPage control.
   • In the Properties Window, change the Name to mpTabs.

Step 3: Add Tabs to the Tab Control

By default, the MultiPage control contains two pages (tabs). You can add more tabs using VBA or manually.

Manually Adding Tabs:

1. Right-click on the MultiPage control.
2. Select « New Page » to add a new tab.
3. Rename each tab using the Caption property.

Using VBA to Add Tabs Dynamically: If you want to create tabs dynamically, you can use the following VBA code:

Private Sub UserForm_Initialize()
    Dim i As Integer
    Dim tabNames As Variant
    tabNames = Array("General", "Settings", "Advanced")
    ' Remove default tabs before adding new ones
    Do While mpTabs.Pages.Count > 0
        mpTabs.Pages.Remove 0
    Loop
    ' Add tabs dynamically
    For i = LBound(tabNames) To UBound(tabNames)
        mpTabs.Pages.Add
        mpTabs.Pages(i).Caption = tabNames(i)
    Next i
End Sub

This code runs when the UserForm loads, removing any existing tabs and adding three new ones: General, Settings, and Advanced.

Step 4: Add Controls to Each Tab

You can manually add controls (labels, textboxes, buttons, etc.) to each tab. Each page acts like a container for controls.

Manually Adding Controls:

1. Click on the MultiPage control.
2. Select a tab (Page1, Page2, etc.).
3. Drag and drop controls from the Toolbox onto each tab.

Adding Controls Using VBA:

You can also add controls programmatically:

Private Sub AddControlsToTabs()
    Dim txtBox As MSForms.TextBox
    Dim lbl As MSForms.Label
    Dim cmdBtn As MSForms.CommandButton
    ' Add a label to the first tab (General)
    Set lbl = mpTabs.Pages(0).Controls.Add("Forms.Label.1", "lblGeneral", True)
    lbl.Caption = "Enter Name:"
    lbl.Left = 10
    lbl.Top = 10
    ' Add a textbox to the first tab (General)
    Set txtBox = mpTabs.Pages(0).Controls.Add("Forms.TextBox.1", "txtName", True)
    txtBox.Left = 100
    txtBox.Top = 10
    txtBox.Width = 150
    ' Add a button to the second tab (Settings)
    Set cmdBtn = mpTabs.Pages(1).Controls.Add("Forms.CommandButton.1", "btnSave", True)
    cmdBtn.Caption = "Save Settings"
    cmdBtn.Left = 10
    cmdBtn.Top = 10
End Sub

This code adds:

• A label and textbox to the first tab (General).
• A button to the second tab (Settings).

Step 5: Write VBA Code to Handle User Actions (Optional)

You may want to handle user interactions such as:

• Switching between tabs.
• Retrieving input values.
• Performing actions when a button is clicked.

Example: Handling Tab Change Event

You can detect when a user switches between tabs:

Private Sub mpTabs_Change()
    MsgBox "You switched to tab: " & mpTabs.Pages(mpTabs.Value).Caption
End Sub

Example: Handling Button Click in a Tab

You can define an event when the Save Settings button is clicked:

Private Sub btnSave_Click()
    MsgBox "Settings Saved!", vbInformation, "Success"
End Sub

Final VBA Code (Complete Version)

Here’s a complete version of the code, combining all the steps:

Private Sub UserForm_Initialize()
    Dim i As Integer
    Dim tabNames As Variant
    tabNames = Array("General", "Settings", "Advanced")
    ' Remove default tabs before adding new ones
    Do While mpTabs.Pages.Count > 0
        mpTabs.Pages.Remove 0
    Loop
    ' Add new tabs dynamically
    For i = LBound(tabNames) To UBound(tabNames)
        mpTabs.Pages.Add
        mpTabs.Pages(i).Caption = tabNames(i)
    Next i
    ' Call function to add controls
    AddControlsToTabs
End Sub

Private Sub AddControlsToTabs()
    Dim txtBox As MSForms.TextBox
    Dim lbl As MSForms.Label
    Dim cmdBtn As MSForms.CommandButton
    ' Add a label to the first tab
    Set lbl = mpTabs.Pages(0).Controls.Add("Forms.Label.1", "lblGeneral", True)
    lbl.Caption = "Enter Name:"
    lbl.Left = 10
    lbl.Top = 10
    ' Add a textbox to the first tab
    Set txtBox = mpTabs.Pages(0).Controls.Add("Forms.TextBox.1", "txtName", True)
    txtBox.Left = 100
    txtBox.Top = 10
    txtBox.Width = 150
    ' Add a button to the second tab
    Set cmdBtn = mpTabs.Pages(1).Controls.Add("Forms.CommandButton.1", "btnSave", True)
    cmdBtn.Caption = "Save Settings"
    cmdBtn.Left = 10
    cmdBtn.Top = 10
End Sub

Private Sub mpTabs_Change()
    MsgBox "You switched to tab: " & mpTabs.Pages(mpTabs.Value).Caption
End Sub
Private Sub btnSave_Click()
    MsgBox "Settings Saved!", vbInformation, "Success"
End Sub

Example Output

When running this UserForm:

• It displays three tabs: General, Settings, Advanced.
• The General tab has a label and textbox for user input.
• The Settings tab has a button to save settings.
• A message box appears when switching tabs.
• Clicking the Save Settings button triggers a message.

Conclusion

This guide provided a detailed step-by-step process for adding a Tab Control (MultiPage) in an Excel VBA UserForm. It covered: ✅ Adding a MultiPage control.
✅ Creating tabs dynamically.
✅ Adding controls to specific tabs.
✅ Writing VBA code to handle user interactions.

Step 1: Prepare the Data

Ensure your data is structured hierarchically. For a Sunburst chart, you will need columns that represent the hierarchical levels, such as:

1. Level 1 (Main Category)
2. Level 2 (Subcategory)
3. Level 3 (Sub-subcategory, etc.)
4. Values (representing the size of each segment)

Example of Data:

Main Category	Subcategory	Sub-subcategory	Value
Category A	Sub A1	Sub-Sub A1.1	10
Category A	Sub A1	Sub-Sub A1.2	20
Category A	Sub A2	Sub-Sub A2.1	15
Category B	Sub B1	Sub-Sub B1.1	25
Category B	Sub B2	Sub-Sub B2.1	30

Step 2: Add VBA Code

Here’s the VBA code to create the Sunburst chart based on the data above.

Step 3: Explanation of the Code

1. Worksheet and Data Range:
   • The code starts by defining the worksheet (ws) where the data is located and calculating the last row with data (lastRow).
   • The dataRange is set to include the data from column A to D, from row 1 to lastRow.
2. Chart Creation:
   • A new chart object is added to the worksheet using ChartObjects.Add, and its position and size are specified with the Left, Width, Top, and Height parameters.
   • The data source for the chart is set using SetSourceData pointing to dataRange.
3. Chart Type:
   • The chart type is set to xlSunburst, which creates a Sunburst chart.
4. Optional Customizations:
   • A title is added to the chart using .HasTitle = True and setting the text for the chart title.
   • Some formatting is applied to the chart using .ApplyLayout (4), which can be adjusted according to your preferences.
   • The legend is moved to the bottom and excluded from the layout.

Step 4: Running the Code

1. Open the Excel workbook where you want the Sunburst chart.
2. Press Alt + F11 to open the VBA editor.
3. Insert a new module (Insert > Module).
4. Paste the above code into the module.
5. Close the editor and return to Excel.
6. Press Alt + F8, select CreateSunburstChart, and click « Run. »

Step 5: Customization

You can modify the code to adjust various aspects:

• Chart Size: Change the Left, Top, Width, and Height parameters when adding the chart.
• Chart Design: Customize the chart design by changing the layout, colors, and legend position

Step 1: Understanding the Requirements

A stopwatch in Excel should:

• Start counting time when triggered.
• Pause and resume when needed.
• Reset to zero.
• Display the elapsed time dynamically.
• Work without freezing Excel (using Application.OnTime instead of DoEvents).

Step 2: Creating the User Interface (UI)

Before writing the VBA code, let’s create a simple UI in an Excel worksheet:

1. Insert Buttons (using Form Controls) and link them to the macro:
   • Start Button (e.g., named « btnStart »)
   • Pause Button (e.g., named « btnPause »)
   • Reset Button (e.g., named « btnReset »)
2. Designate a Cell for Display:
   • Select a cell (e.g., B2) to display the elapsed time.

Step 3: Writing the VBA Code

Now, let’s write the VBA code for the stopwatch.

1. Declare Variables

We need to track:

• The start time
• The elapsed time before pausing
• Whether the stopwatch is running

Option Explicit
Dim startTime As Double
Dim elapsedTime As Double
Dim isRunning As Boolean
Dim nextTick As Date

2. Start Stopwatch

This macro initializes the stopwatch and begins updating the display every second.

Sub StartStopwatch()
    If Not isRunning Then
        ' Capture the start time if not already running
        startTime = Timer - elapsedTime
        isRunning = True
        UpdateTime
    End If
End Sub

Explanation:

• If the stopwatch isn’t running, we capture the start time (Timer is the number of seconds since midnight).
• We subtract the previously recorded elapsedTime (to allow resuming).
• isRunning is set to True and we start updating the time.

3. Update Displayed Time

This subroutine keeps updating the elapsed time.

Sub UpdateTime()
    If isRunning Then
        elapsedTime = Timer - startTime
        Sheet1.Range("B2").Value = Format(elapsedTime, "0.00") & " sec"
        ' Schedule the next update
        nextTick = Now + TimeValue("00:00:01")
        Application.OnTime nextTick, "UpdateTime"
    End If
End Sub

Explanation:

• Calculates elapsed time dynamically.
• Updates the assigned cell (B2).
• Schedules itself to run again in 1 second using Application.OnTime.

4. Pause Stopwatch

This macro stops the timer temporarily.

Sub PauseStopwatch()
    If isRunning Then
        isRunning = False
        Application.OnTime nextTick, "UpdateTime", , False
    End If
End Sub

Explanation:

• Stops Application.OnTime, preventing further updates.
• Stores the elapsedTime so it can resume later.

5. Reset Stopwatch

This resets everything to zero.

Sub ResetStopwatch()
    isRunning = False
    elapsedTime = 0
    Sheet1.Range("B2").Value = "0.00 sec"
    Application.OnTime nextTick, "UpdateTime", , False
End Sub

Explanation:

• Stops the stopwatch.
• Resets elapsedTime to zero.
• Clears the scheduled Application.OnTime events.

Step 4: Assign Macros to Buttons

1. Right-click each button.
2. Select « Assign Macro ».
3. Link them as follows:
   • « StartStopwatch » → Start Button
   • « PauseStopwatch » → Pause Button
   • « ResetStopwatch » → Reset Button

Step 5: Testing the Stopwatch

1. Click Start → The time should begin updating.
2. Click Pause → The time should stop but remain visible.
3. Click Start again → The stopwatch should resume from where it stopped.
4. Click Reset → The timer should reset to 0.

Final Notes

• Application.OnTime ensures Excel remains responsive.
• The format « 0.00 sec » makes the output readable.
• The logic supports pausing and resuming, unlike traditional DoEvents-based loops.