Results 1 to 7 of 7

VBA - Temporarily disable a macro to run another

This is a discussion on VBA - Temporarily disable a macro to run another within the Excel Questions forums, part of the Question Forums category; I have a spreadsheet that I run a query with. I have a simple worksheet event macro that forces cell ...

  1. #1
    Board Regular NBVC's Avatar
    Join Date
    Aug 2005
    Location
    Ontario
    Posts
    5,827

    Default VBA - Temporarily disable a macro to run another

    I have a spreadsheet that I run a query with.

    I have a simple worksheet event macro that forces cell G1 to always be active (that is the input cell for the query to refresh its data based upon)

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Range("G1").Select
    End Sub
    I want to be able to temporarily "disable" that macro in order to click a button on the sheet.

    Currently it won't let me click that button because of the worksheet event macro forcing cell G1 to be active....

    Any suggestions on what I can do?
    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Microsoft MVP - Excel



  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    40,255

    Default

    maybe put application.enableevents = false as the first line in the button's code?
    Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    Board Regular NBVC's Avatar
    Join Date
    Aug 2005
    Location
    Ontario
    Posts
    5,827

    Default

    Yup! That did the trick! I forgot about the Enableevents property...

    Thanks for refreshing my memory Jonmo1
    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Microsoft MVP - Excel



  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    40,255

    Default

    no problem, just don't forget to reset back to true as the last line of code in the button.
    Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  5. #5
    VoG
    VoG is offline
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,652

    Default

    I may be wrong but I think that EnableEvents resets to True when an End Sub is encountered.
    HTH, Peter
    Please test any code on a copy of your workbook.

  6. #6
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    18,250

    Default

    VoG II:
    Nope.

    Try this:
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    MsgBox "Hello"
    End Sub
    If it reset, it should show Hello at every selection change, but as you can see it doesn't.
    Office 2010/2016

  7. #7
    New Member
    Join Date
    May 2006
    Posts
    1

    Default Re: VBA - Temporarily disable a macro to run another

    Old thread - but solution still good. Formulas are not pasting down in a table with the onchange events active. Added a button to disable/enable quickly when the table formulas need updating. Minor but annoying problem having to copy formulas manually on every row. Was issue in Excel 2013.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com