Worksheet_Activate Not Firing

[dthhal]

I have a workbook that contains several worksheets. One particular worksheet is a main page with buttons that open up the other worksheets for the user to view specific data. To minimize the open worksheets I have tried to institute a worksheet activate so that when the user selects this main page tab all other worksheets hide. It works well, for most of the sheets. However, for some reason a few sheets will not hide. Ive tried several different codes to get these particular sheets to close to no avail. Ive tried calling each sheet individually to hide the sheet, and Ive tried to hide all sheets together. Codes Ive used are below. Again, both codes work fine but only on some sheets. How can I get the Activate call to work for all sheets? Anyone ever have this issue? The particular sheets Im having a hard time with are in red in 2nd code.

Codes Ive used:

Private Sub Worksheet_Activate()
'Closes all open sheets and goes back to main menu
           
    Dim sh As Worksheet
    
    For Each sh In ThisWorkbook.Worksheets
       If Not sh.Name = "Reports" Then sh.Visible = False
   Next sh
End Sub

And:

Private Sub Worksheet_Activate()
    Sheets("Pivot32nd_Hr_By Model").Visible = False
    Sheets("Chart32nd_Hr_By Model").Visible = False
    Sheets("PivotMontlyWearVsBilledWear").Visible = False
    Sheets("ChartMonthlyWearvsBilledWear").Visible = False
    Sheets("PivotCostByBrand").Visible = False
    Sheets("ChartCostByBrand").Visible = False
    Sheets("PivotCostByModel").Visible = False
    Sheets("ChartCostByModel").Visible = False
    Sheets("Data50%Below").Visible = False
    Sheets("Pivot50%Below").Visible = False
    Sheets("Chart50%Below").Visible = False
    Sheets("DataFleet%Tread").Visible = False
    Sheets("PivotFleet%Tread").Visible = False
    Sheets("ChartFleet%Tread").Visible = False
    Sheets("PivotCustomerWear_Hr").Visible = False
    Sheets("ChartCustomerWear_Hr").Visible = False

[karan]

There is nothing wrong with the code. Have you checked if there is any code in the sheet modules that is not allowing them to hide? Are you able to hide them manually?

--Karan--

[dthhal]

There isnt any other code in the sheets. All other code is on the main page and assigned to buttons. They close fine manually. I am stumped. Ive put code in the other button macros to close these sheets and they work fine. For some reason the _Activate just isnt picking up these particular sheets.

[DonkeyOte]

Are they Chart sheets ?
Chart sheets do not form part of the Worksheets Collection.

Private Sub Worksheet_Activate()
Dim sh As Object    
For Each sh In ThisWorkbook.Sheets
    If Not UCase(sh.Name) = "REPORTS" Then sh.Visible = False
Next sh
End Sub

[dthhal]

2 sheets are pivot tables and 2 sheets contain pivot charts. The other sheets that work fine are of the same format.

[DonkeyOte]

So... did you test the suggested code ?

[karan]

Can you post a sample workbook? Make sure you remove any sensitive data from it.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

Regards,
Karan

[DonkeyOte]

Karan, I would pretty much stake my life on the fact that changing sh variable from worksheet to Object will resolve the issue.

Neither the Pivot Table nor the Pivot Chart "sheets" will appear within the Worksheets Collection, the former by virtue of being linked to the Chart Object
If you wish to test, were the PT Chart removed leaving only the PT the existing code would work without issue given all "sheets" would then be "worksheets".
(if the PT Chart were embedded as an object within the PT sheet I suspect it would work also)

In these scenarios the basic options are to:

a) iterate Worskheets then ChartObjects

or

b) iterate by Object

[karan]

Thanks for the information DonkeyOte. But, was that message for me?

Regards,
Karan

[dthhal]

DonkeyOte,
Thanks for the change in code, however it didnt work. Another weird thing is that when other sheets that work are open along with the sheets that dont, all will hide properly. But if the ones that dont work are open by themselves it doesnt hide them.

[DonkeyOte]

OK well looks like I'm dead man walking then given my earlier confidence.

Given you've now tested the object route and that is failing please post a sample.

It goes without saying of course that we're all assuming

a) the Activate code resides in the sheet object which is not being hidden
b) the sheet being Activated to invoke the code is called Reports
(that part could be coded differently but what I posted should have worked)

I'm curious regards:

...when other sheets that work are open along with the sheets that dont, all will hide properly. But if the ones that dont work are open by themselves it doesnt hide them.

that doesn't make much sense to me I'm afraid...

[dthhal]

that doesn't make much sense to me I'm afraid...

Me either. I have never seen anything like this before. Give me just a minute to remove some sensitive material and I'll post the example. Thank you all for your help.

[dthhal]

Ok. Heres the example. Thanks again for the help.

[karan]

The Worksheet_Activate event doesn't trigger at all.

That is because Application.EnableEvents has been set to False. In all the modules where you have altered the EnableEvents property to False, please set it to True at the end.

Application.EnableEvents = True

That should solve your problem.

Regards,
Karan

[DonkeyOte]

EDIT: I suspect karan's post above will resolve your issue making below all superfluous... it seems Module 7 is the only affected with the failed resets (error handlers permitting of course)


Sorry, which code am I meant to be looking at exactly ?

I can't replicate your issue at all...

The sheets you referenced earlier in red:

Sheets("PivotCostByBrand")
Sheets("ChartCostByBrand")
Sheets("PivotCostByModel")
Sheets("ChartCostByModel")

and more specifically those beginning with "Chart" are not Chart Objects in the sample file thus they hide without issue (even if they are the only other visible sheets) at the time the Reports sheet is activated - ie all are hidden without problem.

If you can outline the steps we should take in your sample to demo the issue at hand that would help.

[dthhal]

Oh my gosh. Karan, you are absolutely right. How stupid of me. Right in front of my face this whole time. That fixed it. Amazing how things disappear when you look at them for so long. Its nice to get fresh eyes on the issue.

Karan, DonkeyOte thank you both for your time and help.

[splendidus]

Oh my gosh. Karan, you are absolutely right. How stupid of me. Right in front of my face this whole time. That fixed it. Amazing how things disappear when you look at them for so long. Its nice to get fresh eyes on the issue.

Karan, DonkeyOte thank you both for your time and help.

Guys, I have the same kind of issue: Worksheet_Activate is not firing. There's no error message or anything else. BUT the worksheet I need to do this on is a Chart, meaning it's a Chart Sheet / Chart Object or whatever it's called.

Any ideas how to make the magic happen?

[splendidus]

Guys, I have the same kind of issue: Worksheet_Activate is not firing. There's no error message or anything else. BUT the worksheet I need to do this on is a Chart, meaning it's a Chart Sheet / Chart Object or whatever it's called.

Any ideas how to make the magic happen?

Guys, S-O-S! Defcon 1! Please help : ]

[arlu1201]

Splendidus,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.