Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using worksheet_change event (VB/VBA)

    I'm having trouble figuring out how to use this event. I know that it requires some sort of range argument, but non of my help books show how the argument has to be passed....

    Any suggestions?

    Thanks,

    tango

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using worksheet_change event (VB/VBA)

    You don't have to pass an argument, the argument is passed to you, so that you can check which cell(s) changed.

    This is a simple example from the online help:

    Private Sub Worksheet_Change(ByVal Target as Range)
    Target.Interior.ColorIndex = 19
    End Sub

    This will color the interior of each cell that is changed pale yellow. If you want to be more selective, use the Intersect function:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("L37"), Target) Is Nothing Then
    Range("L37").BorderAround LineStyle:=xlContinuous
    End If
    End Sub

    This will put a border around cell L37 as soon as it changes.

  3. #3
    New Lounger
    Join Date
    Mar 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using worksheet_change event (VB/VBA)

    Thanks for the help! I'm still a novice...I'm trying to capture a pivottable change on a sheet (when the user selects a new page value it launches the event). How would you suggest capturing this to use it to launch a macro?

    Thanks again

    tango

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using worksheet_change event (VB/VBA)

    Say that the cell containing the pivot table page field is B1, and that you want to launch a macro named MyMacro when the user selects a different page value.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B1")) Is Nothing Then
    Application.EnableEvents = False
    MyMacro
    Application.EnableEvents = True
    End If
    End Sub

    Turning EnableEvents off, then on is optional. It prevents other events from being processed while your macro runs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •