I would not use ActiveSheet, I would call it out explicitly as noted in the code below.
I also added a ton of comments so you can understand what is going on.
Option Explicit
Private Sub Worksheet_Activate()
'======================================================================
'Declare variables
'======================================================================
Dim TestRange As String 'Used to define the range to check if blank
Dim ClearRange As String 'Used to define the range that will be cleared
Dim ClearTab As String 'used to define the name of the tab that needs the range to be cleared
Dim TestValue As Long 'used to check if testrange is empty - if it is zero, then it is empty
Dim PW As String 'used to define the password
Dim WrkSht As String 'used to define the sheet to unprotect
'======================================================================
'Define Variables
'======================================================================
WrkSht = ActiveSheet.Name 'this defines the active sheet, but I would call it out specifically like the code commented out below
'WrkSht = "WhateverSheet" <- example
TestRange = "B10:B19, B21:B30, B32:B39, B41:B48, B50:B57, B59:B66, B10:B19" 'Defines the range
ClearRange = "A10:N67"
ClearTab = "Job Ticket"
TestValue = WorksheetFunction.CountA(Worksheets("Daily Chgs").Range(TestRange)) 'COUNTA checks for any NONblank cells
PW = "Financial3"
'======================================================================
'Test Logic
'======================================================================
If TestValue < 0 Then 'If any cells are NOT blank then
Worksheets(WrkSht).Unprotect Password:=PW
Sheets(ClearTab).Range(ClearRange).Clear
Worksheets(WrkSht).Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, Password:=PW
'Exit Sub 'Do you want the sub to exit after this or go on to call the next procedure??
End If
'======================================================================
' Call procedure
'======================================================================
Call PopulatesJobTicket.NewDateRange
'======================================================================
'End Macro procedures
'======================================================================
End Sub
You can make this code MUCH smaller, or less text, but when new I prefer to have more visibility into what exactly is going on.
If you have any questions, let me know.
Also check out this website so you can learn how to properly step through code and determine how to correct things.
http://www.cpearson.com/excel/DebuggingVBA.aspx
Bookmarks