+ Reply to Thread
Results 1 to 4 of 4

How to prevent worksheet code from running while Module Macro is running:

  1. #1
    Registered User
    Join Date
    07-12-2014
    Location
    North Carolina
    MS-Off Ver
    Office 2007
    Posts
    79

    How to prevent worksheet code from running while Module Macro is running:

    Hello,
    I hope I can explain this clearly. I have this piece of VBA code that I keep on the worksheet level of a spreadsheet- it highlights the column and row of the selected cell (easier to visual track when using a large sheet). Someone on this forum helped me with it.

    I also have a Macro that has many many steps- the problem I'm running into is that while the Macro runs each cell the Macro is selecting or changing is being highlighted, which is slowing the Macro down substantially (think 30 seconds to run instead of 3 seconds- and this spreadsheet will be used very very often). The macro I'm using is too large to post here.

    How can I prevent the below worksheet level code from running while another Macro is running? I don't need the below worksheet level code to START working until after the Macro is complete- maybe that helps?

    Here's my idea, but I don't know how to execute it- maybe I can change the below sheet-level code to only run when a condition is met (say for example the unused cell Q1 has a value of 1), then I can make the last step of the Macro to place that value (1) in cell Q1, so that the sheet level code will start working only when the Macro is complete? I'm sure there's a better way, that was just what I came up with.

    Thanks in advance for your help. I've been searching online for hours with no results. I'm not incredibly versed in VBA (I'm learning as I go), so thanks for your patience.

    Here's the code that I'm placing on the sheet-level.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim RngRow As Range
    Dim RngCol As Range
    Dim RngFinal As Range
    Dim Row As Long
    Dim Col As Long

    Cells.Interior.ColorIndex = xlNone

    Row = Target.Row
    Col = Target.Column

    Set RngRow = Range("A" & Row, Target)
    Set RngCol = Range(Cells(1, Col), Target)
    Set RngFinal = Union(RngRow, RngCol)

    RngFinal.Interior.ColorIndex = 15

    End Sub

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to prevent worksheet code from running while Module Macro is running:

    In your macro code, use:
    Please Login or Register  to view this content.
    to prevent the worksheet code running and then:
    Please Login or Register  to view this content.
    to re-enable it at the end.
    Remember what the dormouse said
    Feed your head

  3. #3
    Registered User
    Join Date
    07-12-2014
    Location
    North Carolina
    MS-Off Ver
    Office 2007
    Posts
    79

    Re: How to prevent worksheet code from running while Module Macro is running:

    You are my hero. Thanks so much.

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to prevent worksheet code from running while Module Macro is running:

    Glad to help.

    Please don't forget to mark the thread [Solved] using the 'Thread tools' link at the top of the page.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Why do I get an error when running the code for an UserForm from a standard module?
    By kjy1989 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-24-2013, 01:50 AM
  2. Replies: 7
    Last Post: 08-18-2012, 01:24 PM
  3. Prevent functions from running while macro is running
    By palpha32 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-24-2009, 08:42 PM
  4. how to prevent code running when in a worksheet code
    By Corey in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-13-2006, 04:00 AM
  5. [SOLVED] Running Standard Module Code from Dataform
    By Jim May in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-29-2005, 12:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1