vba worksheet events...펌

VBA Course: Worksheet Events

On the last page, we looked at events related to the entire workbook. on this page, we'll move on to focus on events linked to a single worksheet.

Worksheet_SelectionChange

To execute instructions based on events for a particular worksheet, select the sheet in the editor, and then Worksheet :

The SelectionChange event will be added by default. This event is executed whenever the selection is changed :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

For example, the code below adds background colors to one or more selected cells and automatically removes the background color from the previous selection when the selection is changed :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static previous_selection As String

    If previous_selection <> "" Then
        'Removing background color from previous selection :
        Range(previous_selection).Interior.ColorIndex = xlColorIndexNone
    End If

    'Adding background color to current selection :
    Target.Interior.Color = RGB(181, 244, 0)

    'Saving the address of the current selection :
    previous_selection = Target.Address
End Sub

Worksheet_Activate

This event will fire when the worksheet is activated :

Private Sub Worksheet_Activate()

End Sub

Worksheet_Deactivate

This event will fire when another worksheet is activated :

Private Sub Worksheet_Deactivate()

End Sub

Worksheet_BeforeDoubleClick

This event will fire when a cell in the worksheet is double clicked :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

End Sub

Worksheet_BeforeRightClick

This event will fire when the user right clicks on the worksheet :

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

End Sub

Worksheet_Calculate

This event will fire when the data on the worksheet are calculated or recalculated :

Private Sub Worksheet_Calculate()

End Sub

Worksheet_Change

This event will fire when the contents of cells in the worksheet are changed :

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Worksheet_FollowHyperlink

This event will fire when the user clicks on a hypertext link :

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

End Sub

Temporarily deactivate all events

To execute code without firing any events, put it between the following two lines of code :

Application.EnableEvents = False ' => deactivate events
'Instructions
Application.EnableEvents = True ' => reactivate events