Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 785 Times in 719 Posts
    Maud & Bobby,

    A slight modification of Maud's fine code.
    As written it will leave Application Events Turned OFF if you change A10 to 1 and thus will no longer operate and will also disable any other event driven code.
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    '---------------------------------
    'DECLARE AND SET VARIABLES
        Dim rng As Range
        Dim cell As Range
        Set rng = Range("A1:A10")
    '---------------------------------
    'CHECK RANGE AND REMOVE OLD MATCHING VALUE
        If Not Intersect(Target, rng) Is Nothing And Target = 1 Then
            Application.EnableEvents = False
            For Each cell In rng
                If cell.Address = Target.Address Then GoTo skip
                If cell = Target Then cell = ""
    skip:
            Next cell
    
            Application.EnableEvents = True
    
        End If
    
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  2. #17
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    oversight...good pickup but I would rather put it here.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    '---------------------------------
    'DECLARE AND SET VARIABLES
        Dim rng As Range
        Dim cell As Range
        Set rng = Range("A1:A10")
    '---------------------------------
    'CHECK RANGE AND REMOVE OLD MATCHING VALUE
        If Not Intersect(Target, rng) Is Nothing And Target = 1 Then
            For Each cell In rng
            Application.EnableEvents = False
                If cell.Address = Target.Address Then GoTo skip
                If cell = Target Then cell = ""
    skip:
            Application.EnableEvents = True
            Next cell
        End If
    End Sub
    Last edited by Maudibe; 2014-08-21 at 20:39.

  3. #18
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 785 Times in 719 Posts
    Maud,

    I think that is a little inefficient as you are doing a turn on/off for each iteration through the loop rather than only once for the whole loop. There is no need to have events on while going through that loop so why bother with the on/off?
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #19
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    ...because if placed at the end and the code is interrupted mid loop, the likelihood of disabling the events becomes probable and the astute point you initially made become moot. Since interrupts are not disabled, one scenario would be if the user presses crtl-break. However, I do understand what you are driving at.

  5. #20
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 785 Times in 719 Posts
    Maud,

    Good point! Although it would be hard to break in a 10 cell loop...really fast fingers.

    BTW: Application.EnableEvents is not persistent, i.e., restarting Excel sets it back to True.

    This is exactly why I have a macros in my Personal.xls file that fix common problems (such as Events being turned off) at the touch of a key sequence. They come in real handy especially when debugging new code.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #21
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    RG,

    For a 10 loop procedure, yes you would need really fast fingers. But is it really clear that the working copy may not be 1000 cells in the range? And who is to say that additional code may not be appended prior to the line of code in this routine in the future that is error prone or perhaps branches from a call to a second routine. I think it is always better to be safe than sorry.

Page 2 of 2 FirstFirst 12

Posting Permissions

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