Posts by Logit

    Just realized I left out one line of code ...


    Updated code :


    Files

    • parksdata.xlsm

      (33.59 kB, downloaded 6 times, last: )

    Comments:


    There are different ways to accomplish your goal. Presented here is one.


    To provide an answer quickly, I chose to work with macros saved in my "toolbox". This is not a reflection

    on the code you posted.


    This code will function as needed so long as it is used "as is". If the sheet names / locations / index positions are changed, the code

    will not function as needed.


    Files

    • parksdata.xlsm

      (33.95 kB, downloaded 3 times, last: )

    Hmmm ..... if the sheets layout vary from one to another ... if the future brings other changes .... creating a formula or macro

    that will accomplish your goal is not possible.


    To attain the goal .... your workbook will need to have consistency with the DATA tabs and the NAMES tab.


    Specifically with the DATA tabs, the tabs needs to be arranged identical to DATA2 , even though some of the columns may not have entries

    depending on the tab :


    Name Title Hours Metric1 Metric2 Metric3


    Also, there needs to be a NAMES tab, with the names listed in Col A as presently show.


    Is this possible ?

    If the workbook is not storing any data ... perhaps the workbook itself is corrupted. 50 mb is an astronomical size !


    Here are some suggestions given to someone else who was experiencing the same issues as you :


    My 2 cents for whatever it is worth. (Probably not much).

    Almost all of these suggestions were derived from various Excel / Microsoft websites - with the exception of the last one which is personal experience.

    If your code runs 99% of the time without issue, it probably isn't the code syntax (my view).

    Here are a number of suggestions :

    Start Excel in SAFE MODE.

    Run the program and see if the issue occurs again.

    If it doesn't, try disabling any add-ins to Excel.

    Disable all Add-Ins to Excel.

    Change the DEFAULT PRINTER.

    This method seems irrelevant but it is not. As, whenever the user opens an Excel

    Spreadsheet then it internally tries to communicate with the connected Printers

    to check for the compatible margins.

    Recompile Macros

    Open the MS Excel and then navigate to the Developer –> Visual Basic.

    Go to the Tools -> Options. After opening the Options window click on the General tab

    and clear the ‘Compile VBA’. Syntax issues will be revealed.

    Repair Excel

    Re-Install Microsoft Office

    Update or Disable Anti-Virus Software

    Turn off Application.Calculation = xlAutomatic at beginning of macros

    Whenever you update a cell, Excel goes through a process to recalculate the workbook.

    When working directly within Excel you want this to happen 99.9% of the time (the exception

    being if you are working with an extremely large workbook). However, this can really slow

    down your VBA code. It’s a good practice to set your calculations to manual at the begining

    of macros and restore calculations at the end of macros. If you need to recalculate the

    workbook you can manually tell Excel to calculate.

    Code:

    Code
    1. Application.ScreenUpdating = False
    2. Application.Calculation = xlManual
    3. 'Your macro code
    4. Application.Calculation = xlAutomatic
    5. Application.ScreenUpdating = True

    Using DoEvents

    At the beginning of a Loop / End Loop, use DoEvents

    Loop

    DoEvents

    'Your code

    End Loop

    Workbook Corruption

    From personal experience .. sometimes the Excel file becomes corrupt requiring

    a "re-write" of the code to delete the corruption. I have had good success by:

    Copying the macro to Notepad.

    Re-create the sheet involved with the macro.

    Copy/Paste the macro back into the Sheet Module of the re-created sheet.

    Do the same with the Regular Modules. Copy / Paste macro to Notepad.

    Delete the Module. Recreate the module. Paste macro back in.


    Also :


    Sometimes Excel will "write" unseen data to those cells located BELOW all of the cells you can see on each worksheet.

    In other words .... let's say in one of your sheets, you've used from A1 to R67. There are various labels, fields, etc. located

    in that range.


    From A68 and below, Excel may have written non-viewable entries/data is some of the cells. The following macro, paste

    inside your workbook and run like any other macro, will review all the sheets / all the blank areas below the range you

    can see are in use and erase that extraneous data. If the garbage data is there, the macro will delete it and reduce the

    overall size of your workbook.


    MAKE A COPY OF YOUR WORKBOOK AND PERFORM THE 'CLEAN UP' ON THE COPY FIRST !!!!


    If it works and the overall size of the workbook is reduced, you can do the same on the original or rename the copy and use it.



    Another way you can clean up the unseen data is to highlight all of the empty rows BELOW the last used row where you

    can see your original design. Then press the DELETE key or right click and select CLEAR CONTENTS.


    I prefer the manual method of highlighting all the rows and selecting CLEAR CONTENTS. But ... it takes extra effort and time.



    Hope some of this helps.

    Quote

    This is a huge workbook (about 50 meg and has about 60 worksheets and over 500k lines of code

    My first "guess" is that you have finally reached the maximum that Excel is going to let you get away with ... even though the workbook has

    functioned well in the past. The size of your project has, more than likely, finally stressed out Excel to the max.


    Suggestion: See if there is a way to reduce the size of the project to something much, much smaller ... or you may need to go to ACCESS.


    Quote


    I thought by using the on error resume next would cut down on problems.

    The "On Error Resume Next" command is not the preferred manner of handling errors (even though many coders, myself included, use it). The combination of the size of your project and use of the "On Error Resume Next" command has 'hidden / bypassed' any errors that may exist in your code. The command does not eliminate the errors ... it just causes EXCEL to ignore them ... temporarily until something else occurs to 'screw things up'. I would say the size of your project is the thing that has finally 'screwed things up'. This is not a criticism ... please don't take it that way. Simply an honest observation.



    If the project is also storing data, as in a database, of information that the employees are referencing while using the workbook ... and if this data storage is large in size (comprising a large percentage of the 50 megs), you could most likely clear things up by storing all of the data in a separate workbook and change your code to access that second workbook when required.

    If your code includes "On Error Resume Next" .... comment out that line and run the macro again.


    If your code does not include "Option Explicit" at the top of all macro code, outside of all macros, include that statement

    and run your macro again.


    Doing both will insure that Excel is giving you error messages with an explanation.

    .

    I tested this code here and it removes the ThisWorkbook_Open macro :


    Code
    1. Dim wb As Workbook
    2. Set wb = ThisWorkbook
    3. With wb.VBProject.VBComponents("ThisWorkbook").CodeModule '<--- change to ThisWorkbook for that module
    4. .DeleteLines 1, .CountOfLines
    5. End With
    6. MsgBox "Done...", 64

    .

    This is one method ...


    In the Workbook_Open event, place a call to your sub INPUTBOX2.


    The last lines in your INPUTBOX2 sub should include the macro code from this resource : https://www.ozgrid.com/VBA/delete-module.htm and direct

    that code to delete the Workbook_Open module. I haven't tested it here but it should work.


    Another method would be to use a "flag" on a hidden sheet. When the password has been assigned, set the flag to the number 1 ... or the letter A ... or

    perhaps even the word PASSWORD. Then you will need to change your Workbook_Open module to first check for this flag. If the flag exists ... the program

    by-passes the password sheet. The user never sees it. If the flag doesn't exist, the Password sheet is displayed.


    HTH