Requesting a Review of a Spreadsheet

    For the last several months I have been in the process of creating a application for the recording of injuries at the company I work at, for all of our facilities where we track injuries, currently (5).


    The workbook I have created uses both userforms and activex controls. I decided to go this route since it would be easiest to display the information I needed, the purpose of the user forms is for data input only.


    One of the features I am proud of that I have included in this workbook is the login page. I have created this since multiple users will be using this workbook and each will have a different level of access, and that level of access also determines what fields are visible to the user on the main pivot table. To prevent unauthorized access to a user account, after the user enters their username, the program looks at the application user name and if they match, then the user may proceed to enter their password.


    Speaking of Pivot Tables, this report contains 28 different pivot tables. The reason for this is to display the different charts that are available, however the downside is that this tends to bog down the program, especially when adding an injury.


    What I am requesting is a review of the application and any advice to improve its performance. The file itself is about 1 meg in size due to the fact that it has a year and half's worth of data and for two plants.


    The workbook can be accessed via this link: https://drive.google.com/file/…YRVpQbFk/view?usp=sharing


    thank you to anyone to who takes the time to review this.




    Implementation of this program is scheduled for January 2016, so as to give me time for testing and debugging. I am also in the process of creating a manual for the program as well as a tutorial.

    Re: Requesting a Review of a Spreadsheet


    I havnt looked at your file yet as im on a mobile, but what happens if the user disables macros before opening the workbook. Are you still protected?

    Re: Requesting a Review of a Spreadsheet


    For info - as your post is not about a specific issue with Excel, it has been moved to a general forum...


    What version of Office are you running this on...? Am curious about the reference to MicroSoft Office XP Web Components.
    OWC was 'retired' with the introduction of Office 2007...

    Re: Requesting a Review of a Spreadsheet


    I see you're using royUK's datepicker - good choice :)


    Reafidy raises a good point though, for instance the workbook opened in protected view, and then had to have macros enabled manually.


    Another thing, although I've not really tested to not sure if it impacts in any way yet, is that there are 2 references missing when I open it. I'm running Excel 2013 on Windows 8.1 Pro and I don't have the two libraries installed by default so you need to account for that on the end user machines also.


    edit - I was prompted to login in with my account username, I pressed "no" and an error was thrown due to missing reference(s).


    If I get a chance I'll have more of a play around. Good job though :)

    Re: Requesting a Review of a Spreadsheet


    Thank you for all replies, and for moving this to appropriate section.



    Quote

    Reafidy[INDENT]I havnt looked at your file yet as im on a mobile, but what happens if the user disables macros before opening the workbook. Are you still protected?[/INDENT]


    What do you mean by this? Just curious




    Quote from S O;749874

    I see you're using royUK's datepicker - good choice :)


    Reafidy raises a good point though, for instance the workbook opened in protected view, and then had to have macros enabled manually.


    Thank you for bringing that up, One of the steps I intend to cover in my user guide is how to enable macros, for those that do not have macros enabled, however most of the users that will be using this spreadsheet already have macros enabled.



    Quote from S O;749874


    Another thing, although I've not really tested to not sure if it impacts in any way yet, is that there are 2 references missing when I open it. I'm running Excel 2013 on Windows 8.1 Pro and I don't have the two libraries installed by default so you need to account for that on the end user machines also.


    edit - I was prompted to login in with my account username, I pressed "no" and an error was thrown due to missing reference(s).



    What was the error you got? And did you happen get the "Excel Found unreadable content in this workbook..." error? I seem to get that.


    This was made in Excel 2007, on windows 7 pro sp1. Any idea on what the libraries were,and if so is there a way determine what components are using those references?


    Quote from S O;749874


    If I get a chance I'll have more of a play around. Good job though :)


    Thank you, not bad for being somewhat of a vba newbie.


    Since I am the only one that can add/delete users, the best way to test the workbook is to login using my username (awelch), which grants full access.

    Re: Requesting a Review of a Spreadsheet


    The one comment you haven't addressed is: OWC was 'retired' with the introduction of Office 2007.


    You should read this: https://support.microsoft.com/en-gb/kb/972129


    I'm sorry, but I haven't looked at the application too closely because of that, but to deploy this you are going to have to also install OWC if the destination machine is 2007+. I would also be wary of installing it on newer versions and the inevitable consequence of using older technology (that is no longer upgraded/updated) just gets worse as time goes on...


    Quote

    "Excel Found unreadable content in this workbook..."

    Get that too.


    Copy of the repair log:
    <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <logFileName>
    error071440_01.xml
    </logFileName>
    <summary>
    Errors were detected in file 'C:\Users\xxxxxxx\Documents\Injury Report Application V2.0 06-25-15.xlsm'
    </summary>
    <repairedRecords summary="Following is a list of repairs:">
    <repairedRecord>
    Repaired Records: PivotTable report from /xl/pivotTables/pivotTable20.xml part (PivotTable view)
    </repairedRecord>
    <repairedRecord>
    Repaired Records: PivotTable report from /xl/pivotTables/pivotTable23.xml part (PivotTable view)
    </repairedRecord>
    </repairedRecords>
    </recoveryLog>

    Re: Requesting a Review of a Spreadsheet


    OK, any idea on what is causing that issue? And I have removed the OWC reference, and am currently going through and seeing if anything at all has been affected.

    Re: Requesting a Review of a Spreadsheet


    You can see from the repair log what is causing the issues... PivotTable20 & PivotTable23. I would delete those, save the workbook and insert them again if you built them manually, otherwise let the code do it.


    Do you actually use OWC components in the project or is it 'just there because'...?

    Re: Requesting a Review of a Spreadsheet


    Ok, I'll have to look at those pivottables.


    And as far i can remember I do not believe they are used.

    Re: Requesting a Review of a Spreadsheet


    As well as the pivottables, you really need to remove unnecessary references and comment out, or remove, all the code remaining after controls were deleted/renamed


    The first step in checking a project is compiling the thing. It will not compile while controls that no longer exist are referenced (nothing to do the the project references) in the code.


    As far as Project References go, as well as OWC you have references to the HTML Help ActiveX control, Outlook, ActiveX Data Objects. Are these needed? I can't find any reference to them in code (OK, I accept 'future enhancements' might mean some will be needed, but not yet).


    Also, for example, it seems all of the code in Sheet12 are old event handlers for ActiveX controls no longer on the sheet - all of that old code should be commented out at the minimum. Then you might be able to actually compile it.


    You can run the code without compiling, as you must have found out - but compiling first means all code errors are picked up before it gets to your users/testers or whoever.


    Sorry, not being 'down' on you - it is a commendable effort considering you're "...somewhat of a vba newbi" but you have posted it for comments...

    Re: Requesting a Review of a Spreadsheet




    Well I've taken what you said and acted upon it, cleaned up the unnecessary code, and seems to be working ok for now, I have made some changes, and below is the link to the new file.


    Some of the changes I have done include modifying the charts page to where charts are activated by clicking on a tab, I found this easier as it eliminated a tab which had all the charts on it, and I using the "export chart" code to cycle through the charts. This also cut down on file size as well.


    I also fixed the issues with one of the pivottables.



    https://drive.google.com/file/…RZ0xtdXc/view?usp=sharing


    A couple of features I plan on adding are a button for to access the help file (The excel file and associated help documents will be stored on what we call our "Share Drive" which everyone in the company has access to, in its own folder.), and a forgot password button which when activated will display either a message box with the password or send an email to the user.


    So looking forward to more comments.


    Quote

    Sorry, not being 'down' on you - it is a commendable effort considering you're "...somewhat of a vba newbi" but you have posted it for comments...


    No problem, I really appreciate it, that is what I asked for.

Participate now!

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

Register Yourself Login