Macro Fails, 'The macro may not be available in this…'

Question

Hello and thank you in advance for your assistance.

I have some code that I admittedly borrowed from a site. It changes the sheet that is being displayed every X seconds. In my case 3 seconds. When I run it it will change to the next sheet one time and then error out after the 3 seconds.

The error I receive is "Cannot run the macro "C:\users\BenjaminSmith\Desktop\Book1.xlsm'!displaysheets'. The Macro may not be available in this workbook or all macros may be disabled."

Here is the code for my Macro

Sub displaysheets()

ShtNum = ActiveSheet.Index

ShtNum = ShtNum + 1
If ShtNum > Sheets.Count Then
ShtNum = 1
End If
Sheets(ShtNum).Activate
Application.OnTime Now + TimeValue("00:00:03"), "displaysheets"


End Sub

If I remove the line

Application.OnTime Now + TimeValue("00:00:03"), "displaysheets"

I can run the macro over and over and there are no issues. Other than the fact it doesn't continue on its own...

The spreadsheet is an XLSM. MS VBA is 7.0. Excel is 2010.

I am thinking maybe the issue is because the code is recursive?

Thanks for your suggestions.

Answer 1

Further from the comments...

The code didn't work because you didn't paste the code in a module. This is a very common mistake among new programmers. In such a case, Excel is unable to find the code as it searches the module.

@Siddharth Rout I had the code in 'ThisWorkbook' I inserted a module 'Module1' and moved the code there and everything works as expected. What is the difference with these two places?

I would recommend going through Chip Pearson's link HERE

Extract from the link if the link ever rots.

Standard Code Modules, also called simply Code Modules or just Modules, are where you put most of your VBA code. Your basic macros and your custom function (User Defined Functions) should be in these modules. For the novice programmer, all your code will be in standard modules. In addition to your basic procedures, the code modules should contain any Declare statements to external functions (Windows APIs or other DLLs), and custom Data Structures defined with the Type statement.

Your workbook's VBA Project can contain as many standard code modules as you want. This makes it easy to split your procedure into different modules for organization and ease of maintenance. For example, you could put all your database procedures in a module named DataBase, and all your mathematical procedures in another module called Math. As long as a procedure isn't declared with the Private keyword, or the module isn't marked as private, you can call any procedure in any module from any other module without doing anything special.

Workbook And Sheet Modules are special modules tied directly to the Workbook object and to each Sheet object. The module for the workbook is called ThisWorkbook, and each Sheet module has the same name as the sheet that it is part of. These modules should contain the event procedures for the object, and that's all. If you put the event procedures in a standard code module, Excel won't find them, so they won't be executed. And if you put ordinary procedures in a workbook or sheet module, you won't be able to call them without fully qualifying the reference.

User Form Modules are part of the UserForm object, and contain the event procedures for the controls on that form. For example, the Click event for a command button on a UserForm is stored in that UserForm's code module. Like workbook and sheet modules, you should put only event procedures for the UserForm controls in this module.

Class Modules are used to create new objects. Class modules aren't discussed here, except to say that a class module is used to handle Application Event Procedures.

Answer 2

Try : (i use this code)

With Application
    .EnableEvents = True 'needed
    .OnTime EarliestTime:=Now + TimeSerial(0, 0, 3), Procedure:="displaysheets", Schedule:=True
End With

Answer 3

Try to put your timer in a global variable and add it each time you run the function, also configure OnTime to be schedulable

Global tmrTimer1

Sub displaysheets()
     tmrTimer1 = Now + TimeValue("00:00:03")
     'Enable the schedule
     Application.OnTime tmrTimer1 , "displaysheets", , True
End Sub