Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate

  • Here is my problem:


    I have a workbook that has 20+ worksheets all with 9 buttons on it. The buttons are used to navigate around the workbook. This workbook loads VEEEERRRY slow. With all the buttons removed it loads lickity split!
    Since at the moment I need to keep the buttons I have tried to use the following code to remove the buttons on the Worksheet_Deactivate event:



    Then on the activated worksheet the following code puts the buttons back:


    Code
    Private Sub Worksheet_Activate()
        call CreateTopButtons
    End Sub


    Which calls this code from a module:



    The problem is this. It crashes. I cannot figure it out as it does not Debug, it crashes Excel randomly. Can anyone give me some ideas about why?


    Thanks

  • Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate


    Here is some code I use to do something very much like you are asking about:



    gwksDashboardSht is a global variable pointing to one of the sheets in my workbook. It could be Activesheet or any other worksheet object.


    Note that these buttons are the Forms buttons not Activex buttons.


    This code works in Excel 2003 and Excel 2007. I haven't tested it with Excel 2010 but have no reason to believe it won't work there also.


    I call the RemoveButtons routine from Workbook_Open and Workbook_BeforeClose procedures. I call MakeButtons routine from the Workbook_Open event procedure. I call the RemoveButtons from both because I have found there are times when the workbook got saved but the BeforeClose event didn't fire. And buttons seem to accumulate if you are careful about adding and removing.


    If you have any questions please ask. The unasked question never gets answered

  • Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate


    Hi Grunwaldtx


    You actually have 11 buttons not 9 as stated. Anywho, I used the code you provided to come up with something that relevant to your problem. Have a look at the table I set up on Sheet 2. Setting up a matrix will reduce the coding required and increase the speed of your code. Anyways my stab so far.


    Take care


    Smallaman


  • Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate


    [ATTACH=CONFIG]46540[/ATTACH]


    vwankerl,


    That seems to work pretty well. the only problem I see is this.


    When loading a worksheet and then loading the buttons the button that is clisked to goto thate particular worksheet seems to get "frozen" on the sheet. and aslo can crash the program.


    Take a look at the example I have uploaded.

  • Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate


    Why would you want to do this?


    Better to find out the cause of the problem

  • Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate


    I am open to any ideas you may have. I was under the assumption this was the best way to do it. I am utilizing the spreadsheet for up to 10 users right now and they are not at all computer savvy. So I need the navigation of the sheets to be seamless and intuitive. I would love to hear a better way?

  • Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate


    grunwaldtx,


    I have done quite a bit of work managing the User Interface for various Excel applications. I understand the need to do so with multiple users who are either computer illiterate or overly self-confident (to their own detriment). I will look at the code you have sent and see if I can help or give you some direction.


    vwankerl

  • Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate


    If the butons are used to navigate around the workbook why use VBA code? Use hyperlinks attached to shapes

  • Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate


    Quote from royUK;614176

    If the butons are used to navigate around the workbook why use VBA code? Use hyperlinks attached to shapes


    I am open to Almost anything. But let me ask this. Would that save space? And load time? My biggest problem is that it takes so long to load the Workbook, and that seems to be because of the buttons that are on all 30+ sheets. My initial idea was to delete the buttons on Worksheet_Deactivate and load them on Worksheet_Activate. But this has proved to be awfully buggy. Would shapes conserve space and quicken the loading of the workbook?

  • Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate


    Quote from vwankerl;614155

    grunwaldtx,


    I have done quite a bit of work managing the User Interface for various Excel applications. I understand the need to do so with multiple users who are either computer illiterate or overly self-confident (to their own detriment). I will look at the code you have sent and see if I can help or give you some direction.


    vwankerl


    I would be happy to send you the entire workbook for you to peruse. The workbook I uploaded was not the correct one with the login system. My initial problem still persists. I have 30+ worksheets with 11 buttons+ on each sheet to navigate around the workbook, and the load time is excessive. Looking for a way to decrease load time significantly and maintain the ease of use for the layman user.


    If you would like to see the workbook, reply back with an email address and I'll send it.

  • Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate


    Do you have a large number of calculations in the worksheets, especially array formulas? If you want to attach the complete workbook you might be able to use a zipped file or a file sharing site

  • Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate


    Quote from royUK;614296

    Do you have a large number of calculations in the worksheets, especially array formulas? If you want to attach the complete workbook you might be able to use a zipped file or a file sharing site


    I do have a large number formula. There are a "medium" number of array formula, but I have tried to mainly make them dynamically copy into the worksheets when the worksheet is activated and deleted when the worksheet becomes deactive.
    Click this http://www.ge.tt/#!/6ntsrPJ/v/0 to download the current workbook.


    Again, one problem with size and load time seems to be the navigation buttons. I have tried to delete them on worksheet deactivate and load them on activate, but it seems to cause crashes. the cose for that is in the module mButtons.


    Let me know what you think.


    PS Username is: tgrunwald
    Password is : shitload


    sorry about the bad word :(

  • Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate


    I would say the major problem is the number of formulas & arrayformulas.


    Why don't you manually set up the workbook - display tabs, etc and not have it in the code?


    This line won't work & debugs as soon as the workbook opens.


    Code
    .Caption = "Terry Grunwald's T-Max for Automobile Dealerships: " & ActiveWorkbook.CodeName


    You can remove all the buttons replace with shapes that hyperlink to the sheets/named ranges etc. That would reduce your code.


    Try running Rob Bovey's Code Cleaner

  • Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate



    Thank You for the help Roy,


    It's funny you mentioned replacing the buttos with shaapes. I just got done doing that, and it reduced the size of the file by 50%, and decreased load time significantly. What exactly do you mean by manually setting up the workbook - display tabs, etc?


    And why?


    Regards,


    Terry

  • Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate


    Your code in the workbook open event sets up the workbook if I remember right, like hiding sheet tabs, etc. This can be done manually & wouldn't need doing by code

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!