Results 1 to 6 of 6

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
    31,829

    Default

    maybe put application.enableevents = false as the first line in the button's code?
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    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
    31,829

    Default

    no problem, just don't forget to reset back to true as the last line of code in the button.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    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 online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,210

    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
    16,777

    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 2007/2010

    Be responsible for the questions you post. If you don't reply to answered questions, be it just a simple "yes, that worked," or even "thanks," the time someone took to help you goes unrewarded.

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