Activate Sheet Event not triggering?

Shanan212
Shanan212 used Ask the Experts™
on
Private Sub Worksheet_Activate()
    
    Dim aSheet As Worksheet
    Set aSheet = Sheets("CALCULATOR")
    If (ActiveSheet.Name = aSheet.Name) And (aSheet.Range("N1").Value <> aSheet.Range("N9").Value) Then
        MsgBox "Rates not applied! Please press Apply Rates button on ADMIN sheet to apply rates.", vbCritical, "Rates Not Applied!"
    End If
End Sub

Open in new window


Hi,

I have the above sub in coding section of "Thisworkbook"

What I am trying to do is, give a warning message if on a worksheet called "Calculator", 2 numbers are different.

However, this is not triggering when I open the 'Calculator' sheet tab!?

Could you please let me know how to run this and how to run this only once when being swtich from a particular tab?

That is, only run it if the previous tab name is 'Admin' (where I was)

Thanks for any help!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Have you put a breakpoint in your code to determine whether the Worksheet_Activate event is firing?  Try this:

Private Sub Worksheet_Activate()
   
    ActiveSheet.Name = <> "Calculator" then Exit Sub

    set aSheet = ActiveSheet
    If aSheet.Range("N1").Value <> aSheet.Range("N9").Value) Then
        MsgBox "Rates not applied! Please press Apply Rates button on ADMIN sheet to apply rates.", vbCritical, "Rates Not Applied!"
    End If

End Sub
Most Valuable Expert 2011
Top Expert 2011
Commented:
The code must be in the module for the Calculator sheet, not in ThisWorkbook.
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
If you follow Rory's advice and move the code to the Calculator sheet code pane, then you might want to simplify it to:
Private Sub WorkSheet_Activate()
    If Range("N1").Value <> Range("N9").Value Then
        MsgBox "Rates not applied! Please press Apply Rates button on ADMIN sheet to apply rates.", vbCritical, "Rates Not Applied!"
    End If
End Sub

Open in new window

Author

Commented:
Thanks all! Lesson learned :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial