Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Cancel the Selection Change Event (Excel XP)

    I need to use the Selection Change Event to trigger a macro automatically, but it must only do this if the user moves off cell C13. So the macro must never run for any other cells except when the user moves out of cell C13. How do I do this??
    I suspect that the VBA intersect function will be used here, but I have never worked with it before!
    Tx for any assistance.
    Regards,
    Rudi

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

    Re: Cancel the Selection Change Event (Excel XP)

    The problem is that the Target argument of SelectionChange is the cell(s) you move to, not the cell(s) you come from. So you must keep track of the cell(s) you come from yourself, for example by using a static variable:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static rngPrev As Range
    If Not rngPrev Is Nothing Then
    If Not Intersect(rngPrev, Range("C13")) Is Nothing Then
    Application.EnableEvents = False
    MsgBox "Do your thing here."
    Application.EnableEvents = True
    End If
    End If
    Set rngPrev = Target
    End Sub

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cancel the Selection Change Event (Excel XP)

    Amazing...Thx Hans...exactly what I needed!
    Cheers
    Regards,
    Rudi

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Cancel the Selection Change Event (Excel XP)

    One small caveat: if this has to happen every time without fail, you might be better off with a global variable (you could just use a boolean flag) which can then be set in the workbook open event too; otherwise, if the file is saved and closed with cell C13 selected, the procedure will not be triggered the first time you move off the cell.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cancel the Selection Change Event (Excel XP)

    thanks for that input. at this moment the solution is fine as it applies strictly to one workbook and sheet only.
    cheers
    Regards,
    Rudi

Posting Permissions

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