Results 1 to 4 of 4

Thread: Event target range

  1. #1
    Junior Member
    Join Date
    Sep 2012
    Posts
    8
    Rep Power
    0

    Event target range

    I have a spreadsheet setup to run a macro every time there is a change in the worksheet. BUT, I only want this macro to be run automatically if a specific range of cells are changed. I am guessing that the "target" range includes the whole worksheet by default. Is there some way that I can address that so that the macro only runs in that specific absolute range?

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    I moved your post to the Excel Help sub-forum where it will be exposed to more people capable of answering your question.

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by bobkap View Post
    I I am guessing that the "target" range includes the whole worksheet by default.
    Target is the range of cells that were changed (assuming we are talking about a Change event), so you can test if that range has any cells you might be interested in. Normally, you are probably expecting the user to change one cell at a time, so you could do this (where I have made up a range of interest, C3:F10, because you didn't tell us what it actually is)...

    Code:
    If Intersect(Target, Range("C3:F10")) Is Nothing Then Exit Sub
    However, it is possible for a user to paste in values where some or all of the values intersect the area of interest. How to handle this kind of depends on what any remaining code in the Change event, or what your macro does with the cells that were change. If the code in either location needs to work with the cells that actually changed, then a loop might be appropriate...

    Code:
    Dim Cell As Range
    If Intersect(Target, Range("C3:F10")) Is Nothing Then Exit Sub
    ' Since we got here, one or more cells in Target intersected with C3:F10, so...
    For Each Cell In Intersect(Target, Range("C3:F10"))
        ' The Cell variable is a reference to a cell that changed, so your code can do whatever is need to or with it
    Next

  4. #4
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14
    Below is example of what I use - I keep all my default settings in a sheet where the operator can toggle between valid selections for a given variable. In this example what delimiter is used in ASCII files that can be imported, parsed and killed

    This just part of code --- so add the rest as you see fit

    Hope this helps

    Thanks Rasm

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.Column = 3 Then
            With ActiveSheet
                 If .Cells(Target.Row, 1).Value = "FileDelimiter" Then
                    If .Cells(Target.Row, Target.Column).Value = "CSV" Then
                            .Cells(Target.Row, Target.Column).Value = "TAB"
                        Else
                            .Cells(Target.Row, Target.Column).Value = "CSV"
                    End If
                End If
         End With
    xl2007 - Windows 7
    xl hates the 255 number

Similar Threads

  1. Worksheet Change Event
    By jamilm in forum Excel Help
    Replies: 2
    Last Post: 12-29-2012, 12:06 AM
  2. Excel freezing when using .ontimer event
    By Rasm in forum Excel Help
    Replies: 5
    Last Post: 11-26-2012, 12:48 PM
  3. Worksheet_Change event
    By Excelfun in forum Excel Help
    Replies: 2
    Last Post: 11-21-2012, 07:24 AM
  4. Excel to Excel Data transfer without opening any of the files(source or target)
    By Transformer in forum Excel and VBA Tips and Tricks
    Replies: 14
    Last Post: 08-22-2012, 10:57 AM
  5. help with after insert event in Access 10
    By richlyn in forum Access Help
    Replies: 9
    Last Post: 03-03-2012, 10:49 PM

Posting Permissions

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