Excel 2019

Worksheet Delete, Calculate, FollowHyperlink Events

In this tutorial, we’ll discuss the BeforeDelete, Calculate, FollowHyperlink and LensGalleryRenderComplete worksheet events. The Worksheet_BeforeDelete event-handler procedure executes when the worksheet is about to delete. The Worksheet_Calculate event-handler procedure executes after the worksheet is recalculated. The Worksheet_FollowHyperlink event-handler procedure executes when any hyperlink is clicked on the worksheet. The Worksheet_LensGalleryRenderComplete procedure executes when the user selects the Quick Analysis tool.

The worksheet event-handler procedures must be in the code module for that worksheet. Put them somewhere else, and they won’t work. You can quickly access that code window by right-clicking the worksheet’s tab and selecting the View Code:

Worksheet view code window

Worksheet_BeforeDelete

The BeforeDelete  event occurs when the worksheet is about to be deleted. The Worksheet_BeforeDelete event procedure does not have a Cancel argument, so it is not possible to prevent the worksheet from being deleted.

Private Sub Worksheet_BeforeDelete()
 MsgBox ActiveSheet.Name
End Sub

It will print the current worksheet name before deleting the worksheet.

Worksheet_Calculate

This event occurs after the worksheet is recalculated. Lets try a basic calculations by entering 2 in cell A1 and 3 in cell A2. Enter the formula =A1+A2 in cell A3. Next, open the VBE and write the following code:

Private Sub Worksheet_Calculate()
 MsgBox "Recalculated"
End Sub

Switch to the Excel window and modify the entry in cell A1 or A2 on the sheet. Notice that after leaving Edit mode, the Worksheet_Calculate event procedure is triggered and you are presented with a Recalculated message.

The FollowHyperlink event occurs when you click any hyperlink on the worksheet. The Worksheet_FollowHyperlink procedure accepts Target (the Hyperlink object) as the parameter which represents the destination of the hyperlink. In the following example, we retrieve the address of the cell next to the clicked hyperlink and increment the value in that cell by adding value 1:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
 Dim c As Range
 Set c = Target.Range.Offset(0, 1)
 c.Value = c.Value + 1
End Sub

Worksheet_LensGalleryRenderComplete

Worksheet_LensGalleryRenderComplete occurs when the user selects the Quick Analysis tool.

Private Sub Worksheet_LensGalleryRenderComplete()
 MsgBox "Render complete"
End Sub

Advertisement:
Advertisement: