×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VBA delay timer in Excel 2013

VBA delay timer in Excel 2013

VBA delay timer in Excel 2013

(OP)
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.

CODE --> VBA

'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.

RE: VBA delay timer in Excel 2013

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA delay timer in Excel 2013

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

combo

RE: VBA delay timer in Excel 2013

...and loose the ScrollTimer procedure.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA delay timer in Excel 2013

(OP)
@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

CODE -->

Application.Wait 
without throwing error similar to (Ctrl+Break)?

Thanks

djamie

RE: VBA delay timer in Excel 2013

Do you try something like:

CODE -->

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

RE: VBA delay timer in Excel 2013

(OP)
@combo,

I may have miscommunicated. When I execute the

CODE -->

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

RE: VBA delay timer in Excel 2013

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

RE: VBA delay timer in Excel 2013

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: VBA delay timer in Excel 2013

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA delay timer in Excel 2013

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.

RE: VBA delay timer in Excel 2013

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:

CODE

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:

CODE

Private Sub cmdGo_Click()
Call ScrollTimer
End Sub

Private Sub cmdStop_Click()
Call DisableScrollTimer
End Sub

Private Sub UserForm_Terminate()
Call DisableScrollTimer
End Sub 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close