VBA delay timer in Excel 2013 1

[djamie]

I am trying to introduce a delay timer (10s) before calling a subroutine that will scroll my data by 1 column each loop. However, when I run the subroutine, the timer is ignored and the columns are scrolled without delay. Finally I get the following error for each instance (56) that loop is executed.

"Cannot run the macro "D:\SWFreezeStatus.xlsm'!ScrollData', The macro may not be available in this workbook or all macros may be disabled.

I've confirmed macro settings in Excel are set to "Enable all macros" and "Trus access to the VBA projet object model is checked.

'Sub HighlightCells()
'Highlight cells in spreadsheet based on criteria  
'End Sub
'Scroll data to view all columns
Sub ScrollData()
 Dim B As Integer
 Dim WBNcount2 As Integer
   WBNcount2 = Cells(3, Columns.Count).End(xlToLeft).Column
    For B = 1 To WBNcount2
        ActiveWindow.ScrollColumn = B + 1
        Call ScrollTimer
    Next
End Sub
'Set timer to wait 10s before each call to ScrollData()
Sub ScrollTimer()
 Dim gCount As Date
    gCount = Now + TimeValue("00:00:10")
    Application.OnTime gCount, "ScrollData"
End Sub

Thank in advance for any help.

 

[SkipVought]

Hi,

????

Each time you Call ScrollData from ScrollTimer in the OnTime statement, it starts starts the 10 second timer. However, the code in ScrollData continues in the For...Next loop. Meanwhile, after 10 seconds, ScrollData is going to try to run a second time which will cause a third instance to run all concurrent and so on.

????

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

You may need [tt]Application.Wait Now + TimeValue("0:00:10")[/tt] instead, directly in the [tt]ScrollData[/tt] subroutine.

combo

 

[SkipVought]

...and loose the ScrollTimer procedure.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[djamie]

@combo,

Thanks! That did exactly what I needed. Follow up question... is there a subroutine I can map to a button that allows me to break

Application.Wait

without throwing error similar to (Ctrl+Break)?

Thanks

djamie

 

[combo]

Do you try something like:

Sub ScrollData()
 Dim B As Integer
 Dim WBNcount2 As Integer
   WBNcount2 = Cells(3, Columns.Count).End(xlToLeft).Column
    For B = 1 To WBNcount2
        ActiveWindow.ScrollColumn = B + 1
        Application.Wait Now + TimeValue("0:00:10")
    Next
End Sub

What do you get? Any error message? Try to save the file, close and reopen excel.

combo

 

[djamie]

@combo,

I may have miscommunicated. When I execute the

application.wait

subroutine. It works as intended. However, while running there will be times when I want to pause or stop the scrolling to review data in a particular column (this will be used as a dashboard). In this case, Excel will not allow user input as it is "locked up" for duration of routine. Only way to interrupt is by doing Ctrl+Break which results in a VBA "error" window stating code execution has been interrupted with options to "continue", "end", "debug" or "help".


djamie

 

[combo]

Maybe it would be better to change the way you scroll the worksheet, either to scroll it manually or display small modeless userform with buttons for scrolling the sheet.

combo

 

[Andrzejek]

If you want to stop the execution of your code during the [tt]Application.Wait[/tt], I don't think you can. What you can do is stop the execution either before or after the [tt]Application.Wait[/tt] line of code.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

...which is going to be a miniscule fraction of the 10 second delay.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DjangMan]

What about a shorter Wait time within a loop where you count up 40 quarter seconds before scrolling. That gives you the ability to 'interrupt' the loop and do the editing. It's not the greatest solution. I'd be tempted to put an "auto-scroll" button on the Ribbon with a timer. That way the code isn't within the worksheet and can be turned on/off by the user if you have to edit something.

 

[combo]

Precising my idea with the userform:
- create userform (I named it frmScroll), make it modeless (either set ShowModal property to False or show it with vbModeless argument),
- add commandbutton (I named it cmdGo), make it Default,
- add commandbutton (I named it cmdStop), make it Cancel,
- resize form and make it small, arrange buttons so the cmdGo is visible, cmdCancel is outside form's visible area.
I used excel vba timer, not presise, but simple. I haven's set any specific limits for scrolling.
You need to run ShowScrollingForm and next click tne button. To pause scrolling press ESC key, continue by clicking the button again.

The code:
- standard module:

Public gCount As Date, bStopScrolling As Boolean, iCounter As Integer
Public iColumn As Integer
Public Const iDurationInSeconds As Integer = 2

Public Sub ShowScrollingForm()
iColumn = ActiveCell.Column
MsgBox iColumn
iCounter = 0
frmScroll.Show
End Sub

Public Sub ScrollTimer()
If iCounter = iDurationInSeconds Then
    iCounter = 0
    Call ScrollData
End If
gCount = Now + TimeSerial(0, 0, iDurationInSeconds)
iCounter = iCounter + 1
Application.OnTime EarliestTime:=gCount, Procedure:="ScrollTimer", Schedule:=True
End Sub

Public Sub ScrollData()
    iColumn = iColumn + 1
    Application.StatusBar = iColumn
    ActiveWindow.ScrollColumn = iColumn
End Sub

Public Sub DisableScrollTimer()
On Error Resume Next
Application.OnTime EarliestTime:=gCount, Procedure:="ScrollTimer", Schedule:=False
End Sub

- userform's module:

Private Sub cmdGo_Click()
Call ScrollTimer
End Sub

Private Sub cmdStop_Click()
Call DisableScrollTimer
End Sub

Private Sub UserForm_Terminate()
Call DisableScrollTimer
End Sub