VBA – Events

Events and Event Procedures were introduced in Excel97. Earlier versions of Excel do not support events.  An Event is an action initiated either by user action or by other VBA code.

 Workbook Open Event

  1. Open the Visual Basic Editor. (Shortcut- F11)
  2. Double click on This Workbook in the Project Explorer.
  3. Choose Workbook from the left drop-down list. Choose Open from the right drop-down list.
  4. Add the following code line to the Workbook Open Event:

MsgBox “Welcome to VBA World!!!”

  1. Save, close and reopen the Excel file.

You will get Message Box Saying “Welcome to VBA World !!! “ at the very beginning once you will open the workbook.

Example to create follow-up worksheet on regular basis

Private Sub Workbook_Open()

Range(“C2”).Activate

Do Until ActiveCell.Value = “”

If (ActiveCell.Value = Date) Or (ActiveCell.Value = Date + 1) Then

ActiveCell.Interior.ColorIndex = 3

ActiveCell.Font.Bold = True

ActiveCell.Offset(0, 1).Value = “Follow Me”

End If

ActiveCell.Offset(1, 0).Activate

Loop

End Sub

Example: Worksheet Change Event

Code added to the Worksheet Change Event will be executed by Excel VBA when you change a cell on a worksheet.

  1. Open the Visual Basic Editor.
  2. Double click on a sheet (for example Sheet1) in the Project Explorer.
  3. Choose Worksheet from the left drop-down list. Choose Change from the right drop-down list.
  4. The Worksheet Change Event listens to all changes on Sheet1.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Value > 80 Then MsgBox “Goal Completed”

End Sub

Example: Worksheet- BeforeDoubleClick Event

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

Target.Font.Color = vbRed

End Sub

Example: Adding a comment when a cell value is changed

Public preValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

Target.ClearComments

Target.AddComment.Text Text:=”Previous Value was ” & preValue & Chr(10) & “Edited on ” & Format(Date, “mm-dd-yyyy”) & Chr(10) & “By ” & Environ(“UserName”)

End Sub

2 thoughts on “VBA – Events

  1. Negative unwanted effects are what most Blue Pill patrons get even when it’s legitimate that options
    are sure. From advanced male surgical therapies proper all the way down to over-the-counter supplements,
    they’re the identical males that search the answer to demoralizing erection failure encountered for a very long time.

    Because of the Blue Pill’s degree of recognition, different respected pharmaceutical laboratories designed their
    very own variation. Every boasting matching or much more encouraging results.
    And probably with an underpinning financial goal, all these
    Viagra wanna be assert that their product include zero uncomfortable side
    effects. But, regardless of the ensures, the multitude of males struggling with unwanted side effects hardly stops increasing.

    Quite the opposite, plant primarily based Viagra options
    if taken in the correct concentrations are sometimes the merchandise dwelling
    actual to this “zero aspect-results” craze. With the information of The Blue Pill’s seemingly detrimental results,
    anyone who would like to wish his erectile efficiency
    improved ought to see his well being care supplier first.

    Horny Goat Weed, an previous various amongst grownup males from The far east is attending to be a wanted natural Viagra alternative
    all through the world. As a result of it has zero or possibly much less important unfavourable
    results, in the present day’s ED patients is now capable of
    finally breathe of consolation. While many different unreliable distributors of this pure Viagra claims it does not have any undesirable
    results, I personally see them drastically incorrect.
    A rise within the physique’s temperature I imagine is considered a aspect impact.

    The Effectivity of Horny Goat Weed
    Concerned in Horny Goat Weed’s much less recognized
    but important edge is strengthening the particular
    person’s skeletal construction. Having said this, people who often have supplements containing this potent plant other than having
    enhanced efficiency and a strong physique as
    properly. Along with this, it’s a nourishing help for these having very poor cardiovascular
    health. The broad number of well being advantages will get prolonged if
    results towards bettering recollection and liver cleansing is added.

    The Work of Horny Goat Weed
    Icariin, the efficient chemical substance gathered in every Horny Goat
    Weed leaf do wonderful issues in re-energizing blood cells.
    Clinically verified of this herbs job is pumping recent oxygenated blood
    stream for that additional productive food regimen and way of life.

    For grownup males recognized as having a pre-current coronary heart downside, all the time be
    good in having that journey to your medical skilled.
    The proper step you are able to do is carry to an finish consuming pills
    which can be furnishing you with solely harmful in addition to unsafe unfavorable results.
    The primary motive it really isn’t beneficial for weak hearted males is
    contemplating that Horny Goat Weed produces an impact which can get your bloodstream to
    operate correctly and understanding that you simply do undergo earlier than a weak coronary
    coronary heart downside outcomes might probably performs in your
    draw back. With that mentioned of a herb structured erectile dysfunction therapy,
    simply how fatal may or not it’s for synthetic remedies like
    Viagra?

Leave a Reply

Your email address will not be published.

Enjoy the tutorial? Please spread the word :)

Follow by Email
Facebook
Facebook
Google+2k
Google+
http://hirdeshbhardwaj.com/2017/06/03/vba-events
YouTube39
YouTube