Consulting

Results 1 to 5 of 5

Thread: How to turn off "activate when cell changed" while other macros change cells?

  1. #1
    VBAX Regular
    Joined
    Oct 2017
    Posts
    22
    Location

    How to turn off "activate when cell changed" while other macros change cells?

    Excel Version: Professional Plus 2013

    I've made a macro for a spreadsheet that displays information and respective dates from data log.
    I also wanted to make it so that any changes made on the display would save to the log.
    The macro would fire everytime a different macro ran, so I had to wrap all the other macros in "Application.EnableEvents = False/True".
    However, now its not even firing.
    How would I make a macro run each time a cell is changed, but not have it activate when other macros change a cell?

    The macro for log update:
    Sub Worksheet_Change(ByVal Target As Range) '''''''''''''''''''''''''''''''    SetProjectVariables
        requestorNum = Range("B3").Value
        
        If Not Intersect(Target, Range("B6:K120")) Is Nothing Then
            rowActiveCell = ActiveCell.Row
            colActiveCell = ActiveCell.Column
            
            ws3log.Cells(rowActiveCell - ws3First + ws3logFirst, colActiveCell + 1 + 10 * (requestorNum - 1)) = ActiveCell.Value
        End If
    End Sub

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    In the other macros

    Application.EnableEvents = False
    
    ….. Updates ….
    
    Application.EnableEvents = True
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Some Macro is exiting before the EnableEvents = True line

    I see a lot of that, so I have a very handy Sub where I can get to it easily
    Sub ResetApplication()
      With Application
        .EnableEvents = True
        .ScreebnUpdating = True
        .Calculation = xlAutomatic 'Technically, this is redundant, but Ram is cheap.
        'Add other as you see fit
      End With
    End Sub
    It is my opinion that it is bad practice to use an Event sub, Like Worksheet_Change, to do anything other than look at the Target(s) and call the appropriate Sub.
    Sub Worksheet_Change(ByVal Target As Range)    
    Application.EnableEvents = False
    
        If Not Intersect(Target, Range("B6:K120")) Is Nothing Then
          SetRequesterNum Target
       'ElseIf Target.Address = "$Z$99:$Z$101" Then 
          'AnotherSub Target
       'ElseIf SomeOther Target Then
          'Some other sub Target
        End If
    
    Application.EnableEvents = True
    End Sub
    Private Sub SetRequesterNum(Target As Range)
       requestorNum = Range("B3").Value
    
            rowActiveCell = Target.Row
            colActiveCell = Target.Column
            
            ws3log.Cells(rowActiveCell - ws3First + ws3logFirst, colActiveCell + 1 + 10 * (requestorNum - 1)) = Target.Value
    
    End Sub
    '------------------------------------------------------------------------------
    
    Private Sub AnotherSub(Target as Range)
       'Do stuff to or with Target, (Range("$Z$99:$Z$101"...) Supposedly ;)
       'For example... X = WorksheetFunction.SUM(Target)
    End Sub
    '--------------------------------------------------------------------------------
    
    Private Sub SomeOtherSub(Target As Range)
    '
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    VBAX Regular
    Joined
    Oct 2017
    Posts
    22
    Location
    Wow, ResetApp() worked like a charm. Thank you!

    I looked around, but couldn't find the open Application.EnableEvent, but using ResetApp(), that is no longer a problem.

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Try hard to find the bug in your code
    In Each Sub (or Function) use a simple MsgBox before Events = False and Events = True
    MsgBox"Setting Events in Sub Name"
    ...Events = False
    '
    'regular sub code
    '
    MsgBox"Resetting events in Sub Name"
    ...Events = true
    That being said, some people use a Standard module, with a name like "Common_Procedures" to hold many often used routines. Then, they just Drag or Import that module into every Project. That is a good place for your Private Sub ResetApplication()... NOTE!!! ResetApplication should only ever be manually triggered and only when you have buggy code.

    One Sub you can reasonably put in Common_Procedures is
    Public Sub SpeedUp(ResetApp As Boolean)
    With Application
       .EnableEvents = Not ResetApp
       .Calculation = Not ResetApp
       .ScreenUpdating = Not ResetApp
       'Add others as you need
    'If you have timing issues because ie: Calculations take too long, add
    'DoEvents
    End With
    End Sub
    This Sub replaces every EnableEvents type code in your other code. To use it
    Sub MyProcedure()
    SpeedUp True
    '
    'Regular Code
    '
    SpeedUp False
    End Sub
    SpeedUp True is mnemonic for "Yeah, I want my code to run faster than usual"
    SpeedUp False is mnemonic for "Ok, Run the application normally."

    Some people think FastCode is a better mnemonic than SpeedUp. YMMV
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Tags for this Thread

Posting Permissions

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