Worksheet_Activate() code not working

[philwojo]

So I am working on a sheet for another group and it has the following code on a worksheet. But the code is not running at all when the sheet is activated.

It was originally just this code:

Private Sub Worksheet_Activate() Call UpdateCodes
End Sub

I updated it to this for testing and I never get a Message Box to pop up:

Private Sub Worksheet_Activate()ActiveSheet.Unprotect "xxxxx"
MsgBox "It is running"
 Call UpdateCodes
ActiveSheet.Protect "xxxx"
End Sub

Any thoughts on what could be causing the code not to run? I click out of the sheet and into another sheet and back again and nothing happens. Just trying to figure out what might be causing this one, seems it should be working to me.

 

[jkpieterse]

Perhaps events are switched off? try exectting this in the immediate window:

Application.EnableEvents = True

 

[philwojo]

Tried that and no change, here is the code I have now to make sure I am doing what you expected though.

Private Sub Worksheet_Activate()Application.EnableEvents = True
ActiveSheet.Unprotect "water"
MsgBox "It is running"
 Call UpdateCodes
ActiveSheet.Protect "water"
End Sub

The message box never even triggers.

 

[Fluff]

Did you put

Application.EnableEvents = True

Into the immediate window, or just into your code?

 

[philwojo]

Just into my code, not sure what you mean by Immediate Window, I might just be missing what that term means.

I am right clicking on the tab in question and doing the View Code to get to this section, that is where I added this in. There is other code that is part of the workbook itself though, not sure if that is impacting this, I wouldn't think so since when I click on this tab (sheet) it should run, isn't that correct?

 

[Fluff]

At the bottom of the code window there may be a small window called Immediate. If not Ctrl G should make it appear.
Paste that one line of code into that window & hit enter.
Then try to activate the sheet again

 

[philwojo]

OK, that wasn't showing, but I hit the CTRL-G and then put it in there.

I then tried again and still nothing happens when I click on the tab.

 

[Fluff]

OK, are macros enabled? and are you sure that the code is in the correct sheet module?

 

[philwojo]

Yes they are enabled and yes I'm sure it is in the correct sheet module, but always good to check.

I inserted a text box and linked the "called" macro from this sheet to that box. When I click on that the code runs just fine and the sheet updates as expected. The people that use it though were used to just having it be updated when they clicked on the tab, not having to click an extra button.

I'm wondering if it is some kind of internal add-in or something our company is using that might be causing it, that is totally a guess, just trying to figure out what else it could be.

 

[Fluff]

Last option I can think of. Try closing & reopening Excel, to see if that resolves it.