Posts by ML!

    Re: Protecting sheets and code...best practice via code...function??


    Thanks Dave...as I was typing a followup, I got your reply which looks like it might fully address item 2. I will let you know if I have questions. It looks brilliant.


    ***


    Just BTW I thought I'd post the error that that was thrown (when the code was unprotected) in case anyone searches on this topic and/or error:


    ERR.NUMBER: 1004
    ERR.DESCRIPTION: The cell or chart that you are trying to change is protected and therefore read-only.
    To modify a protected cell or chart, first remove protection using the Unprotect Sheet command (Review tab, Changes group). You may be prompted for a password.


    Which is why I raised the question :)

    Looking for some advice...I am used to coding in Access and this is my first foray into creating an app of sorts in Excel. I want my users to use controls and userforms to do stuff like add or revise data in sheets. I want a nice clean interface so wanted to protect cells so they couldn't be selected. I have a dashboard with main menu controls and a chart that is updated as users add and/or edit data in the supporting worksheets. There is one sheet where the user needs some flexibility so they can click on a button to 'unlock' a limited number of cells.


    I got it all ready for deployment, hid the sheets that the user shouldn't see, protected the non-hidden sheets and added a password to protect the code because I'm a bit of a hack and don't want any users to see my rather inelegant code.


    I sent it to the primary user for testing confident in my awesome error trapping. It includes not only code to write to an error log including a timestamp, error numbers and description to a hidden sheet but also sends a copy of that error log to me via email.


    Turns out you can't insert or update data on a protected sheet and error trapping doesn't work with password protected code(?!)


    An error was thrown and trapped but instead of the the msgbox I wanted shown, a VBA error was displayed:


    "Compile error in hidden module: frmDetails. This error commonly occurs when code is incompatible with the version, platform, or architecture of this application."


    I did some research and found that it might be a library reference that was missing, or that the code was unavailable.


    I sent her a copy of the workbook without password protected code and replicated the error which displayed my error msg and wrote to the error log sheet.


    So I need advice on

    • How to implement error trapping when code is password protected
    • Code to unprotect and unprotect every time a visible sheet is activated
    • Would you put that code in a function and call it from the activate event? I can't use the workbook open event because, as mentioned, I don't want users to be able to select cells or ranges.


    I'm excited to hear what creative solutions others have done in similar circumstances. Any and all discussion is more than welcome.


    TIA all
    ML!

    Re: Change sum range depending on today's date


    Thank you A...


    I appreciate such a quick response and great ideas. I am going to try to implement using your suggestions and if I can't make it work, I will do as you recommend and upload a dummy.


    Cheers!
    ML

    I have a budget worksheet that uses a spending forecast sheet to determine where the spending level for each budget code should be to the end of the current month. In other words, the Forecasted YTD To Month End fomula for each budget code in the budget worksheet is simply:


    =SUM(Forecast!D8:F8)

    where column D is Jan, E is Feb, F is Mar etc in the forecast which distributes the budgeted amounts by month.


    In each row on the budget sheet I want to evaluate today's date and if we're in March I want the formula to read as above but when we move to April I want it to use G as the end column in the range to sum.


    SUMIF seems cumbersome in this circumstance and I wonder if a UDF might suit better. Any thoughts/help?


    TIA
    ML!