+ Reply to Thread
Results 1 to 3 of 3

Exit a running macro elegantly

  1. #1
    Forum Contributor
    Join Date
    02-24-2008
    Location
    UK
    MS-Off Ver
    2012 and 2016
    Posts
    123

    Exit a running macro elegantly

    I have produced the following macro which schrolls a worksheet up and then down the screen a set number of times (3 in the following example). However, there are times when I want to exit this macro before the macro has completed its run. Pressing the Escape key produces the usual error/debub message box - not very elegant. Can anyone help me to stop the macro when hitting the escape key without getting this error/debug message?

    Sub Scroll()

    Sheets("Summary").Select 'applies macro to Summary sheet only

    Dim x As Single
    Dim Arr As Variant
    Dim Lr As Long, i As Long, j As Long, k As Long, sec As Long, z As Long

    Lr = Cells(Rows.Count, "B").End(xlUp).Row - 19 'originally "1" but then it scrolled too far
    Arr = Array(1, -1) 'set up array containing 2 values to control direction of scroll (up or down)
    j = 0 'initialise scroll count
    k = 3 ' number of times the scores scroll down and up
    sec = 3 'pause (in seconds) before scrolling next row

    Range("A6").Select 'moves cursor to first unfrozen row

    Do
    For z = 0 To UBound(Arr)
    For i = 1 To Lr
    ActiveWindow.SmallScroll Down:=Arr(z)
    x = Timer
    While Timer - x < sec 'number of seconds pause (approx)
    Wend 'repear while procedure until Timer condition met
    Next i
    Next z
    j = j + 1
    Loop Until j = k 'number of times scroll down and up
    End Sub

  2. #2
    Forum Contributor
    Join Date
    03-25-2008
    MS-Off Ver
    Excel, Outlook, Word 2007/2003
    Posts
    245
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    02-24-2008
    Location
    UK
    MS-Off Ver
    2012 and 2016
    Posts
    123

    Thanks

    Many thanks for your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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