Forum Discussion
VBA Macro Error with application.ontime
Hello everyone,
I am having an issue getting a macro to run while using application.ontime. The macro itself works if i call it alone, but i get the error (Attached Snip) stating that the macro is either "Not available" or "All macros are disabled." I know this not to be true, because the macro works alone, while all the other macros work fine as well. Please see code below:
Sub RefreshAll()
Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Set wb = ThisWorkbook
Set ws1 = wb.Worksheets("C200 Telework Report")
Set ws2 = wb.Worksheets("Totals")
Set ws3 = wb.Worksheets("Gantt")
Set ws4 = wb.Worksheets("Compliance")
ws1.Unprotect Password:="xxx"
ws2.Unprotect Password:="xxx"
ws3.Unprotect Password:="xxx"
ws4.Unprotect Password:="xxx"
wb.RefreshAll
'Move to Protect Sheets
Application.OnTime Now + TimeValue("00:00:05"), "NoTouch" 'Error in code here
End Sub
__________________________________________________________________________________________________
Sub NoTouch()
Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Set wb = ThisWorkbook
Set ws1 = wb.Worksheets("C200 Telework Report")
Set ws2 = wb.Worksheets("Totals")
Set ws3 = wb.Worksheets("Gantt")
Set ws4 = wb.Worksheets("Compliance")
ws1.Protect Password:="xxx", AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
ws2.Protect Password:="xxx", AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
ws3.Protect Password:="xxx", AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
ws4.Protect Password:="xxx", AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
ws1.Visible = xlSheetVisible
ws1.Activate
ws1.Range("N12").Activate
Call HideAllWorkSheets
End Sub
______________________________________________________________________________________________________
Sub HideAllWorkSheets()
Dim ws As Worksheet
Dim CurrentWS As String
CurrentWS = ActiveSheet.Name
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> CurrentWS Then
ws.Visible = xlSheetVeryHidden
End If
Next ws
End Sub
Are you sure that the macro NoTouch is stored in a standard module (the kind you create by recording a macro or by selecting Insert > Module in the Visual Basic Editor) in the same workbook? It won't work if NoTouch is stored in a worksheet module or in the ThisWorkbook module.
5 Replies
- The VBA Macro Error message appears when there is an error in the macro that you were running.
The specified method cannot be used on the specified object for one of the following reasons:
An argument contains a value that is not valid. A common cause of this problem is trying to gain access to an object that does not exist; for example, Workbooks(5), when only three workbooks are open.
The method cannot be used in the applied context. Specifically, some Range object methods require that the range contain data. If the range does not contain data, the method fails.
An external error occurred, such as a failure to read or write from a file.
(This issue does not apply to Mac) A method or property cannot be used because of security settings. For example, the properties and methods of the VBE object for manipulating the Visual Basic for Applications (VBA) code that is stored in a Microsoft Office document are inaccessible by default.
To turn on trusted access to Visual Basic Projects, do the following:
Enable the Developer tab on the ribbon. See Show the Developer tab for more information.
On the Developer tab, in the Code group, click Macro Security.
The Code group on the Developer tab
Under Developer Macro Settings, select the Trust access to the VBA project object model check box.
Regards,
Rachel Gomez- Your comments have taught me a lot; however, this was not the fix. thank you for the help, though. I did still learn something from your reply.
Are you sure that the macro NoTouch is stored in a standard module (the kind you create by recording a macro or by selecting Insert > Module in the Visual Basic Editor) in the same workbook? It won't work if NoTouch is stored in a worksheet module or in the ThisWorkbook module.
- Thank you! It is small items like this that put me in check. I start to think I know a good amount, and then I pull an error like this and start to realize that I have only scratched the surface...
- I do not have time to try right now because of training today, but I do know that it is in a worksheet, and not one that I created. I will try to move it when I get out of training. Thank you.
