Étiquette : macro_events

An example featuring three buttons: CANCEL, RETRY, and IGNORE, accompanied by a warning icon. The associated VBA code is as follows:

Sub MsgBoxAbortRetryIgnore()
    Dim response As Integer
    response = MsgBox("An error occurred during the save process." _
        & vbCrLf & "Do you want to cancel the operation?" _
        & vbCrLf & "Do you want to retry the operation?" _
        & vbCrLf & "Do you want to ignore this message?", _
        vbAbortRetryIgnore Or vbExclamation, _
        "Save Error")     
    If response = vbAbort Then
        MsgBox "You chose to cancel the operation."
    ElseIf response = vbRetry Then
        MsgBox "You chose to retry the operation."
    Else
        MsgBox "You chose to ignore the message."
    End If
End Sub

Explanation:

This example demonstrates how to create a message box with three distinct buttons — Cancel, Retry, and Ignore — that allows the user to respond to a critical situation such as an error during a save process. These buttons provide the user with options to either cancel the operation, retry it, or ignore the warning altogether.

The message box also includes a warning symbol (the exclamation mark icon) to clearly indicate the seriousness of the message. When the user clicks one of the buttons, the code detects the choice via the variable response and then displays a corresponding confirmation message reflecting the user’s selection.

The Worksheet_Calculate() event in Excel VBA is triggered whenever a recalculation occurs on a worksheet. This makes it ideal for automating post-calculation adjustments, such as formatting or layout updates.

Example Code: Worksheet_Calculate

Private Sub Worksheet_Calculate()
    Range("G:G").Columns.AutoFit
End Sub

Explanation

What is Worksheet_Calculate()?

• This is a worksheet-level event that runs every time any formula on the sheet is recalculated.
• The recalculation can be triggered by:
  • A user changing the value of a cell that another formula depends on
  • A manual recalculation (F9)
  • VBA code that modifies data on the worksheet

Example Scenario

Suppose cell G3 contains the formula:

=G1 + G2

If the user changes either G1 or G2, Excel recalculates G3, which in turn triggers the Worksheet_Calculate event.

What This Code Does

• Automatically adjusts the width of column G to fit its current contents:

• Range(« G:G »).Columns.AutoFit

• This ensures that if formulas in column G return values of varying length, the column resizes dynamically to make all content fully visible.

Key Notes

• The event is only triggered when recalculation occurs — not simply when data is typed into unrelated cells.
• If there are no formulas involved or calculation dependencies, this event will not run.

Use Case Ideas

• Automatically resizing result columns (like in reports or dashboards)
• Triggering formatting or layout updates after calculations
• Highlighting cells with updated results
• Exporting or logging recalculated values

Summary Table

Feature	Details
Event	Worksheet_Calculate
Trigger	Any recalculation involving formulas on the worksheet
Code effect	Adjusts column G’s width to fit new content
Where to place	Worksheet’s code module (Sheet1)
Cancelable?	❌ No
Common Uses	Formatting, resizing, dynamic refresh, data validation

 

You can respond to a user double-clicking a cell by using the Worksheet_BeforeDoubleClick() event. This event lets you control or cancel the default behavior (which is entering cell editing mode), and instead apply custom formatting or logic.

Example Code: Worksheet_BeforeDoubleClick

Private Sub Worksheet_BeforeDoubleClick( _
    ByVal Target As Range, Cancel As Boolean)
    Target.Interior.Color = vbGreen
    Target.Borders.LineStyle = xlContinuous
    Target.Borders.Weight = xlThick
    Cancel = True
End Sub

Explanation:

What is Worksheet_BeforeDoubleClick()?

• It is a worksheet-level event that runs whenever a user double-clicks a cell.
• The code must be written inside the worksheet’s code module (e.g., Sheet1).
• You can cancel the default double-click behavior using the Cancel parameter.

Parameters:

• Target As Range:
  Refers to the specific cell (or range) that was double-clicked.
• Cancel As Boolean:
  A flag that determines whether Excel should proceed with the default action (i.e., entering edit mode in the cell).

What This Code Does:

1. Formats the clicked cell:
   • Background color → Green:

• Interior.Color = vbGreen

1. • Border style → Solid, thick:

• Borders.LineStyle = xlContinuous
• Borders.Weight = xlThick

Prevents the cell from entering edit mode:

Cancel = True

Setting Cancel = True cancels the default action, so the cell is formatted but not edited.

Behavior Notes:

• If you omit Cancel = True, Excel will proceed with its default behavior after running your code — that means the cell will enter edit mode.
• This event only triggers when the user performs a true double-click on a cell.
• If the user double-clicks a cell that is merged, Excel might raise an error unless handled properly.

How to Test It:

1. Paste the code into the sheet module for Sheet1.
2. Open the workbook and navigate to that sheet.
3. Double-click on any cell.
4. You should see the cell turn green with a thick border, and no cell editing starts.

Summary

Feature	Details
Event	Worksheet_BeforeDoubleClick
Trigger	User double-clicks on a cell
Target	Cell that was clicked
Cancel	Set to True to prevent default editing behavior
Use cases	Formatting, custom pop-ups, redirecting input, preventing edits

This event is perfect for scenarios where you want to respond visually or trigger custom logic when users interact with specific cells.

The Worksheet_SelectionChange() event allows you to execute code whenever the user selects a different cell or cell range on a specific worksheet.

Example Code: Worksheet_SelectionChange

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox Target.Address
End Sub

Explanation:

What is Worksheet_SelectionChange()?

• This is a worksheet-level event that triggers each time a new selection is made by the user on the worksheet.
• The procedure must be placed in the code module of the specific worksheet (e.g., Sheet1), not in a standard module or ThisWorkbook.

Parameters:

• Target As Range:
  This parameter is automatically passed by Excel and represents the newly selected range as a Range object.

What This Code Does:

• It shows a message box with the address of the selected range, using:

• MsgBox Target.Address

• .Address returns the absolute reference of the selection (e.g., $A$1, $C$3:$D$5).

How to Test It:

1. Insert the code into the module for Sheet1.
2. Open the worksheet Sheet1.
3. Use your mouse or keyboard to select various cells or ranges.
4. Each time you make a new selection, a message box will appear showing the selected range.

You can also select non-contiguous ranges using Ctrl + Click.
The full address (e.g., $A$1,$C$3) will be shown.

Summary

Feature	Details
Event name	Worksheet_SelectionChange
Trigger	User selects a new cell or range
Parameter (Target)	The newly selected range (Range object)
Common uses	Interactive feedback, data validation, logging, dynamic UI

This event is ideal for:

• Displaying selection info
• Triggering dynamic changes (e.g., highlight, validate)
• Logging user activity

 

The Worksheet_Activate() event allows you to define actions that should occur every time a specific worksheet is activated, whether by user interaction or through VBA code.

Example Code: Worksheet_Activate Event

Private Sub Worksheet_Activate()
    MsgBox ActiveSheet.Name
    Range("C5").Select
End Sub

Explanation:

What is Worksheet_Activate()?

• It is a worksheet-level event, triggered when the user (or VBA) activates a specific worksheet.
• The procedure must be placed in the code module for the target sheet, e.g., Sheet1— not in ThisWorkbook or a standard module.

What This Code Does:

Displays a message box showing the name of the activated sheet:

MsgBox ActiveSheet.Name

Selects cell C5 on that worksheet:

Range(« C5 »).Select

When Does the Event Trigger?

• Every time the worksheet is activated, either:
  • Manually by the user (e.g., clicking on the sheet tab)
  • Programmatically via:

• Worksheets(« Sheet1 »).Activate

Important Note:
The event will not fire again if the sheet is already active. It only triggers when switching from another sheet.

How to Test It:

1. Insert the code into the module for Sheet1.
2. Switch to another sheet (e.g., Sheet2).
3. Then click back on Sheet1.
4. A message box appears, and cell C5 is selected.

Summary

Feature	Details
Event name	Worksheet_Activate
Module required	The worksheet’s module (e.g., Sheet1)
Trigger	The sheet is activated (not already active)
Sample actions	Show message, select cell, update data, refresh visuals

This event is useful for:

• Automatically selecting key cells
• Triggering refreshes or recalculations
• Resetting the UI when navigating between sheets

 

The following VBA procedure ensures that a workbook is automatically saved when the user attempts to close it — without Excel asking whether to save changes.

Example Code: Workbook_BeforeClose Event

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.Save
End Sub

Explanation:

What is Workbook_BeforeClose()?

• Workbook_BeforeClose is an event procedure that executes just before the workbook is closed.
• It must be placed in the ThisWorkbook module, not in a standard module.
• The Cancel parameter allows you to interrupt the closing process (e.g., based on a condition), but it’s unused here.

What This Procedure Does:

• Calls:

• Save

This saves the workbook immediately without displaying the default “Do you want to save changes?” dialog box.

• Because this occurs before Excel prompts the user, the normal save confirmation is skipped — making the process fully automatic.

Behavior Without This Procedure:

Normally, when a user makes changes and tries to close the workbook:

• Excel displays a confirmation dialog:

“Do you want to save the changes you made…?”

With this macro in place:

• The workbook is saved silently, and the close operation continues without interruption.

How to Test It:

1. Place the code in the ThisWorkbook module of Mappe2.xlsm.
2. Open the file and make a visible change (e.g., edit a cell).
3. Close the workbook.
4. No prompt will appear — the file is saved automatically and closes.

Summary

Feature	Details
Event name	Workbook_BeforeClose
Module	Must be in ThisWorkbook
Purpose	Save workbook automatically on close
Prevents save prompt	Yes
Parameter	Cancel As Boolean (optional — can be used to cancel close)
Save method used	ThisWorkbook.Save

If needed, you can enhance this event to include conditions (e.g., only save if specific cells are changed) or to log the save operation.

 

The following VBA procedure is designed to automatically execute when a specific workbook is opened. In this example, it shows a message with the name of the current workbook and opens another workbook from a specified location.

Example Code: Workbook_Open Event

Private Sub Workbook_Open()
    MsgBox ActiveWorkbook.Name
    Workbooks.Open "C:\Users\POPOLY\document_name.xlsm"
End Sub

Explanation:

What is Workbook_Open()?

• Workbook_Open() is a workbook-level event that runs automatically when the workbook is opened.
• It must be placed inside the ThisWorkbook module, not in a regular module or sheet module.
• This makes it useful for initializing settings, showing messages, loading data, or opening other workbooks.

What this Procedure Does:

Displays a message box showing the name of the workbook that was just opened:

MsgBox ActiveWorkbook.Name

Opens another workbook located at:

Open « C:\Temp\Mappe1.xlsm »

This assumes that the file exists at the given path.

How to Test It:

To see this code in action:

1. Close all Excel instances completely.
2. Open the file Mappe2.xlsm (which contains this macro in ThisWorkbook).
3. When the file opens:
   • A message box will show the workbook name (Mappe2.xlsm).
   • Then, Mappe1.xlsm will open automatically from C:\Temp.

✅ Important:

• Ensure macros are enabled when you open xlsm.
• The path C:\Temp\Mappe1.xlsm must be correct and accessible.
• You may need to adjust the file path depending on your environment.

Summary

• Use Workbook_Open() in ThisWorkbook to trigger actions when a workbook opens.
• Ideal for loading additional workbooks, initializing settings, or preparing the interface.
• Combine it with error handling (e.g., On Error Resume Next) for robustness if the file doesn’t exist.