How can I run a macro in one sheet without it having an effect on other spreadsheets I open

Question

I'm running a macro in one workbook which is constantly running. When I open another workbook the macro breaks and its falls out of range. How can I lock the macro on that particular sheet? macro code is:

Sub startTimer()
On Error Resume Next
ActiveSheet.Unprotect Password:="***"
Application.OnTime Now + TimeValue("00:00:01"), "Increment_count", Schedule:=False
Application.OnTime Now + TimeValue("00:00:01"), "Increment_count"

End Sub

Sub Increment_count()
If ActiveCell.Column = 9 Then
    ActiveCell.Value = ActiveCell + 1
    startTimer
Else
    MsgBox "Timer works only in I column", vbCritical, "Error"
    End If
End Sub

Sub stopTimer()
On Error Resume Next
Application.OnTime Now + TimeValue("00:00:01"), "Increment_count", Schedule:=False
ActiveSheet.Protect Password:="***"
ActiveWorkbook.Save
End Sub

start by not using `ActiveWorkbook` and `ActiveSheet`, use referenced objects instead. — Shai Rado, Nov 07 '16 at 10:58
take out `On Error Resume Next` and address the errors as they come. This way your code will be more robust — CallumDA, Nov 07 '16 at 11:54

score 1 · Answer 1 · edited May 23 '17 at 11:48

This answer is an extension of the comment by Shai Rado

Activeworkbook and Activesheet reference to the sheet currently focussed on. A better way is to name your worksheet of interest and get that worksheet by its name.

Dim correctSheet as Worksheet
correctSheet = Worksheets("SheetName")
correctSheet.Unprotect ' etc..

Look also to this question:Declaring variable workbook / Worksheet vba

This reference might also be useful http://www.excel-easy.com/vba/workbook-worksheet-object.html

How can I run a macro in one sheet without it having an effect on other spreadsheets I open

1 Answers1