Closed Thread
Results 1 to 20 of 20

Worksheet_Activate Not Firing

  1. #1
    Registered User
    Join Date
    07-31-2009
    Location
    Gilbert, Az
    MS-Off Ver
    Excel 2007
    Posts
    30

    Worksheet_Activate Not Firing

    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:
    Please Login or Register  to view this content.
    And:
    Please Login or Register  to view this content.
    Last edited by dthhal; 02-10-2010 at 04:48 PM. Reason: Code

  2. #2
    Forum Contributor
    Join Date
    04-18-2009
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    267

    re: Worksheet_Activate Not Firing

    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--

  3. #3
    Registered User
    Join Date
    07-31-2009
    Location
    Gilbert, Az
    MS-Off Ver
    Excel 2007
    Posts
    30

    re: Worksheet_Activate Not Firing

    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.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    re: Worksheet_Activate Not Firing

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

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-31-2009
    Location
    Gilbert, Az
    MS-Off Ver
    Excel 2007
    Posts
    30

    re: Worksheet_Activate Not Firing

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

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    re: Worksheet_Activate Not Firing

    So... did you test the suggested code ?

  7. #7
    Forum Contributor
    Join Date
    04-18-2009
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    267

    re: Worksheet_Activate Not Firing

    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

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    re: Worksheet_Activate Not Firing

    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

  9. #9
    Forum Contributor
    Join Date
    04-18-2009
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    267

    re: Worksheet_Activate Not Firing

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

    Regards,
    Karan

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    re: Worksheet_Activate Not Firing

    Yep... ie I'm not really sure we need a sample file - I would like OP to test suggestion first off - as yet no response to that.

  11. #11
    Registered User
    Join Date
    07-31-2009
    Location
    Gilbert, Az
    MS-Off Ver
    Excel 2007
    Posts
    30

    re: Worksheet_Activate Not Firing

    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.

  12. #12
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    re: Worksheet_Activate Not Firing

    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:

    Quote Originally Posted by dthhal
    ...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...

  13. #13
    Registered User
    Join Date
    07-31-2009
    Location
    Gilbert, Az
    MS-Off Ver
    Excel 2007
    Posts
    30

    re: Worksheet_Activate Not Firing

    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.

  14. #14
    Registered User
    Join Date
    07-31-2009
    Location
    Gilbert, Az
    MS-Off Ver
    Excel 2007
    Posts
    30

    re: Worksheet_Activate Not Firing

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

  15. #15
    Forum Contributor
    Join Date
    04-18-2009
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    267

    re: Worksheet_Activate Not Firing

    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.

    Please Login or Register  to view this content.

    That should solve your problem.

    Regards,
    Karan

  16. #16
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    re: Worksheet_Activate Not Firing

    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:

    Quote Originally Posted by dthhal
    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.
    Last edited by DonkeyOte; 02-10-2010 at 04:43 PM.

  17. #17
    Registered User
    Join Date
    07-31-2009
    Location
    Gilbert, Az
    MS-Off Ver
    Excel 2007
    Posts
    30

    re: Worksheet_Activate Not Firing

    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.

  18. #18
    Registered User
    Join Date
    05-09-2012
    Location
    USA
    MS-Off Ver
    2019 16.0.13205.200000 64-bit
    Posts
    61

    Re: Worksheet_Activate Not Firing

    Quote Originally Posted by dthhal View Post
    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?
    Office 2019 16.0.13205.200000 64-bit

  19. #19
    Registered User
    Join Date
    05-09-2012
    Location
    USA
    MS-Off Ver
    2019 16.0.13205.200000 64-bit
    Posts
    61

    Re: Worksheet_Activate Not Firing

    Quote Originally Posted by splendidus View Post
    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 : ]

  20. #20
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Worksheet_Activate Not Firing

    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.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1