Activating sheets in Worksheet_Change

June 14, 2010 at 16:05:40
Specs: Windows XP

When 1 sheet changes, I try to activate another sheet and make a change to it. However, the other sheet won't activate. I've used this syntax to activate sheets for years. Perhaps VBA won't let me switch sheets during a Worksheet_Change?

Code is:

CurrentSheet = ActiveSheet.Name
Worksheets("Inquiry").Activate
InquiriesRowsCount = ActiveSheet.UsedRange.Rows.Count
ClosedCount = 0
OpenedCount = 0
Today = Date
Application.EnableEvents = False
For i = 2 To InquiriesRowsCount
If (Today - Cells(i, 6) <= 7) Then
ClosedCount = ClosedCount + 1
End If
Next
Worksheets("Stats").Activate
Application.EnableEvents = False
Cells(58, 2) = OpenedCount
Cells(59, 2) = ClosedCount
Application.EnableEvents = True
Worksheets(CurrentSheet).Activate

Why?

Chuck


See More: Activating sheets in Worksheet_Change

Report •


#1
June 14, 2010 at 16:30:49

First, if you use the pre tags found above the Comments box, and paste your code between them, it makes it easier to read.

See my code below.

Second, your code does activate the sheets. I added two lines to prove it.

Third, why do you set Application.EnableEvents = False twice?

Private Sub Worksheet_Change(ByVal Target As Range)
 CurrentSheet = ActiveSheet.Name
  Worksheets("Inquiry").Activate
'New line
     MsgBox ActiveSheet.Name
    InquiriesRowsCount = ActiveSheet.UsedRange.Rows.Count
    ClosedCount = 0
    OpenedCount = 0
    Today = Date
  Application.EnableEvents = False
     For i = 2 To InquiriesRowsCount
      If (Today - Cells(i, 6) <= 7) Then
       ClosedCount = ClosedCount + 1
      End If
     Next
  Worksheets("Stats").Activate
'New line
    MsgBox ActiveSheet.Name
'What's this next line for?
    Application.EnableEvents = False 
     Cells(58, 2) = OpenedCount
     Cells(59, 2) = ClosedCount
    Application.EnableEvents = True
 Worksheets(CurrentSheet).Activate
End Sub


Report •

#2
June 14, 2010 at 18:12:45

Thanks for the reply. The problem is that the 2 lines that start with "Cells(58, 2) =" are not writing to the sheet I activated ("Stats"), but rather to "Inquiry".

The 2nd Application.Enable Events = False is just a line left from tests I was doing to resolve this problem. It should have no effect.

Chuck


Report •

#3
June 14, 2010 at 19:08:38

Well, let's start with the fact that in most cases you don't have to Activate a Sheet (or Select a Range) to perform a VBA action on it. That's an inefficient way to write code.

If you want to write to the Stats sheet, just use this:

Sheets("Stats").Cells(58, 2) = OpenedCount
Sheets("Stats").Cells(59, 2) = ClosedCount

Next, I'm going to hazard a guess and say that this code resides in the Inquiry sheet.

I say this because VBA doesn't really care which sheet is Active when you use instructions like Cells(58, 2) = OpenedCount. It's going write to Cells(58, 2) in the sheet in which the code resides.

Therefore, there doesn't appear to be any need to activate any sheets in your code. Simply refer to the sheet (by Name or by Position number) you want to perform the action on as I did above.


Report •

Related Solutions

#4
June 15, 2010 at 19:14:31

Thanks for the fix. My belief that the active sheet would be the fault for Cells(x,y) is now past history.

Report •

#5
June 15, 2010 at 19:25:52

Well, hold on a minute.

Don't toss that belief too far!

If the code resides in a sheet module, like a Change event macro would, then Cells(x,y) will indeed "ignore" the ActiveSheet and refer to the sheet in which it resides.

However, if the code resides in a Standard Module, i.e. Module1, then Cells(x,y) will refer to Cells(x,y) on the ActiveSheet.

I apologize that I did not explain that in more detail earlier.


Report •


Ask Question