CODEWITHSUNDEEP
vbaexcelmacros
Justinas
Justinas

Reputation: 1

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

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

Upvotes: 0

Views: 90

Answers (1)

pvl
pvl

Reputation: 194

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

Upvotes: 1

Related Questions