1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Secure cells

Discussion in 'Business Applications' started by snoozee, Apr 19, 2018.

Advertisement
  1. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,474
    First Name:
    Hans
    I like Kiwi (the fruit) :)
    I hope it works will wait and see where I can help after you tested and checked it out..
    I learned some new things while working on this file too so, you see the forum helps both ways
     
  2. snoozee

    snoozee Thread Starter

    Joined:
    Apr 10, 2006
    Messages:
    242
    LOL where are you from ?
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,474
    First Name:
    Hans
    Minor necessary corrections, monthly is shown now too but the Daily if the month is in the past and nothing has been completed should also be an X not a clock
     

    Attached Files:

  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,474
    First Name:
    Hans
    Short version; I'm Dutch, born on CuraƧao, grew-up in the tropics (Central America) lived an worked in the several African countries and have been living in the Netherlands since '87
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,474
    First Name:
    Hans
    Update: I've been busy with weekly and monthly There are some cells with invisible values (formatting to show nothing) which server as helper for the calculations.
    I can imagine you've run into issues when trying to modify the contents of the different sheets because of the event triggers
    If you open the VBA editor and select the immediate window just type in stopallevents and strike enter, this will block all the event triggering so you can select a worksheet unprotect it and modify it
    Once you've finished you type resetallevents and then just press the HOME button on the active worksheet and all will be 'normal'again.

    I modified the Weekly sheet layout (I hate merged cells ) and added the calculation so the ween ending is displayed correctly and the month name is blank as long as the month name for that date is the same as the one before.
    This will also make it more dynamic since weeks tend to move around as the dates change
    The KBMe is an issue since you have a Due Date so I split that up; the cell under the month name holds the month's number (e.g. 2 for February) so the month name is displayed above it but June July is a split year June belongs to the year before and also in some you you mention a starting day and an end day, that will have to be clear so that code can be written for it, and formulas might fall short, so either VBA or some helper formulas in the empty columns next to it.

    BTW: in the VBA module XIUDFRequiredModule01 is a UDF named ResetWorkBookSheets() if you run this one it clears all the worksheets and rebuilds all, the _Daily worksheets. So if you edit something do this i the Empty_Daily and then run this macro so all the _Daily sheets will have the changes implemented like the counting of completed , pending etc

    Have fun with this one. :) You can create the 2018-2019 by pressing the button on the Home sheet
     

    Attached Files:

  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,474
    First Name:
    Hans
    Your stalker again :)

    Weekly update on Home sheet seems to work
     

    Attached Files:

  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,474
    First Name:
    Hans
    Compliance Learning as well as I could
     

    Attached Files:

  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,474
    First Name:
    Hans
    My Avatar reflects it all a little country of Birth, lived, studied ...
     
  9. snoozee

    snoozee Thread Starter

    Joined:
    Apr 10, 2006
    Messages:
    242
    I've been away all week with work so to log in and see all the work you have been doing is just mind blowing !!!

    I'm so looking forward to having a play :)

    I've been to a lot of places yet not the Netherlands so no wonder you're a sleep when I'm awake LOL
     
  10. snoozee

    snoozee Thread Starter

    Joined:
    Apr 10, 2006
    Messages:
    242
    I have a question :)

    The "clock" was meant to represent that the task was still on track to be completed e.g. it's in the future so just wondering why some of the characters are appearing as "crosses'.
    upload_2018-5-18_14-56-31.png
     
  11. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,474
    First Name:
    Hans
    I just used your result in the daily sheets, in this case the formula will need an additional test for the year it refers to.
    If you check your (original) formula in the K12 fields (in each Daily sheet), that one was not modified by me. Oversight on my part, good you some brain gymnastics on your part :)
    So if the year / month combination is in the past or the whole year is in the past and outside the coverage it should be a X
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,474
    First Name:
    Hans
    I guess this will do, give it a test.
    Delte the 2018-2019 file if you have it and create a new one using this file.
     

    Attached Files:

  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,474
    First Name:
    Hans
    I did some checking and I think the formulas in the daily sheet will need to be reviewed including extra IF conditions.
    If you look at this week in May_Daily the current week shows incomplete (X) for today instead of the clock which will appear if you select one task but it should also show if the day is not over and nothing is entered.
    A lot fine-tuning or write additional UDF's that can be used for each Daily sheet to accurately show the calculation result(s)
    Work for the drawing board and some additional brain gymnastics :)
    Hope all's well down under, we should be starting Summer here but some days look like Autumn :)
    Play around with the update I attached yesterday, and your day is about over on your side of the globe.
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...

Short URL to this thread: https://techguy.org/1208904

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice