+ Reply to Thread
Results 1 to 7 of 7

UnCall a sub routine

Hybrid View

shiftyspina UnCall a sub routine 02-12-2015, 08:20 AM
mehmetcik Re: UnCall a sub routine 02-12-2015, 08:26 AM
shiftyspina Re: UnCall a sub routine 02-12-2015, 09:06 AM
mehmetcik Re: UnCall a sub routine 02-12-2015, 09:15 AM
shiftyspina Re: UnCall a sub routine 02-12-2015, 10:25 AM
shiftyspina Re: UnCall a sub routine 02-12-2015, 10:12 AM
mehmetcik Re: UnCall a sub routine 02-12-2015, 01:27 PM
  1. #1
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Crayford, kent
    MS-Off Ver
    Excel 2013
    Posts
    394

    UnCall a sub routine

    Hi

    I have a code

    Private Sub Worksheet_Activate()
    Call FlashBack
    End Sub
    Which works fine, but I need it to stop when selection changes?

    I have tried STOP, END, UNCALL, but cant get this to work? what is the code needed to stop FlashBack Sub?

    Thanks

    Dan

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: UnCall a sub routine

    Please post a copy of Flashback
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Crayford, kent
    MS-Off Ver
    Excel 2013
    Posts
    394

    Re: UnCall a sub routine

    Sub FlashBack()
    
     Dim newColor As Integer
     Dim myCell As Range
     Dim x As Integer
     Dim fSpeed
    
     Set myCell = ActiveCell
     Application.DisplayStatusBar = True
     Application.StatusBar = "... Select Cell to Stop and Edit or Wait for Flashing to Stop! "
     newColor = 27
     fSpeed = 0.5
     Do Until x = 20
    
     DoEvents
     Start = Timer
     Delay = Start + fSpeed
     Do Until Timer > Delay
     DoEvents
     myCell.Interior.ColorIndex = newColor
     Loop
     Start = Timer
     Delay = Start + fSpeed
     Do Until Timer > Delay
     DoEvents
     myCell.Interior.ColorIndex = xlNone
     Loop
     x = x + 1
     Loop
     Application.StatusBar = False
     Application.DisplayStatusBar = Application.DisplayStatusBar
     End Sub

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: UnCall a sub routine

    Ok you are asking the user to select a cell to stop the macro.

    So at the top of your module declare a public variable

    Public StopFlag as Integer
    
    Sub Auto_Open()
    StopFlag = 0
    End Sub
    Now you need to Set the Stop Flag

    so right click on the sheet name at the bottom of excel and select view code

    paste this code there.

    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    StopFlag = 1
    End Sub

    Now modify your code .

    
    Sub FlashBack()
    
     Dim newColor As Integer
     Dim myCell As Range
     Dim x As Integer
     Dim fSpeed
    
    StopFlag = 0
    
     Set myCell = ActiveCell
     Application.DisplayStatusBar = True
     Application.StatusBar = "... Select Cell to Stop and Edit or Wait for Flashing to Stop! "
     newColor = 27
     fSpeed = 0.5
     Do Until x = 20
    
     DoEvents
     Start = Timer
     Delay = Start + fSpeed
     Do Until Timer > Delay
    
    If StopFlag = 1 then exit Sub
    
     DoEvents
     myCell.Interior.ColorIndex = newColor
     Loop
     Start = Timer
     Delay = Start + fSpeed
     Do Until Timer > Delay
    
    If StopFlag = 1 then exit Sub
    
     DoEvents
     myCell.Interior.ColorIndex = xlNone
     Loop
     x = x + 1
     Loop
     Application.StatusBar = False
     Application.DisplayStatusBar = Application.DisplayStatusBar
     End Sub

  5. #5
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Crayford, kent
    MS-Off Ver
    Excel 2013
    Posts
    394

    Re: UnCall a sub routine

    is there a way to only run this code if it is called from say worksheet1, rather than when I open worksheet2 which is where the flashback occurs?

    I have hyperlinks which link to cells I want to flash, but if go into this worksheet then it still runs, I would ideally like to remove that

  6. #6
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Crayford, kent
    MS-Off Ver
    Excel 2013
    Posts
    394

    Re: UnCall a sub routine

    That is spot on thank you very much

  7. #7
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: UnCall a sub routine

    The selection change macro is sheet specific

    so put it in the sheet where you want it to act.

+ 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. Pass a variable from one sub-routine to another sub-routine
    By gowtham_pec in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-01-2013, 07:07 PM
  2. Sub Routine Name
    By stoney1977 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-19-2011, 04:52 PM
  3. Why won't this routine run?
    By cmcgath in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-17-2009, 03:41 PM
  4. This routine just keeps on going! Why?
    By LFCFan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-20-2006, 11:18 AM
  5. [SOLVED] VBA routine (101)
    By Ray in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-26-2005, 03:05 PM

Tags for this Thread

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