Posts by chester1993

    Hello team. Looking for help to secure/protect my attached workbook. The workbook, obviously, has macros all over the place.

    What I am looking to happen is the following:


    Main Tab: Hide all cells except for range D1:P60 when workbook open. Will also hide all other sheets unless admin logged in.

    Allow selection of the following but prevent edits from other user other than admin (when logged in): Months in Arrow, M3:M11, the whole calendar D4:J58, row 60 (D:M), P5:P7, Admin Login button.


    I have tried the protect/un-protect or locked/unlocked cells but I think I am doing it wrong. My macros would bug out when run on a protected sheet.

    Files

    Oh. I placed the code on the wrong sub. Lol. Placed the worksheet code under Worksheet_SelectionChange instead of Worksheet_Change. That's a stupid mistake. Hahahahaha. Will try to transfer the code. Currently, the file force close when I transfer the code.


    Edit:

    Yeah. I transfer the code and Excel just close everytime. 😭

    That's the thing, I am not really enhancing it, per se. The column #s are almost identical to each other. The difference is the sheet #. I had problems initially with the Appointment scheduler, so what I did is re-do my design and replicate what Randy did. It did work at the end, but it took some time for me to correct the codes again. Haha.


    On this case, there is a big difference to the range Randy is using on his example (Workbook Sharing) compared to the range of the calendar as it is dynamic.


    For instance, on the Global Workbook Sharing file, the range he declared is D5:K18 and this range isn't dynamic. Meanwhile, on the appointment scheduler there is a range where you put entry's to the calendar, you also have a range for the calendar itself, you also have a database at the back end where those entries are saved. I think this is where the problem is on my project. At least, to my very little knowledge, that's where the problem is.


    So as much as I want to really just replicate Randy's work, it's not really letting me do it. Hahaha.

    Hello y'all. Need help with the attached file.


    I've seen this tutorial from YT about Global Workbook sharing and applied it to my current project. However, it is not working as intended - atleast on my file. I downloaded the excel file from that tutorial and its working fine - also attached the file here.


    On the file Draft 4, entries on the calendar should be entered through M4:M9. After that, the entry would appear on the calendar reflecting the date on M3. As you can see, if you select any of the dates on the calendar, the date on M3 would also change. The LoadDay() macro enable the user to load all entries on each day and show it on column M4:M9 when a day with entry is selected.


    What's the issue I am having?


    I copied the code from the workbook tutorial and change references to match what I need for my project. According to the tutorial, the code below SHOULD create all folders for the user under "Admin" tab and create a Notepad recording changes for the defined range. In my case, it only creates a folder for the "Current User" instead of creating a folder for ALL user and it does not create any Notepad file for the changes made on the range.


    The macro below allow the "Synch" button to run and reflect all changes from the original workbook to the copy workbook.

    I really hope someone could help me figure out what I am doing wrong here. I would really appreciate it.

    Initially, I think I have a problem with the range I declared below. But, I am not sure how to move forward.

    Code
    1. If Not Intersect(Target, Range("M11:M18")) Is Nothing Then

    Thank you so much guys!

    Try this out.


    1. You can select to show Status or All items.

    2.You can change the status of requests whatever status is displayed.

    3. You probably need to be able to add new requests

    I'm lost at #3. Sorry. Haha.


    I tried the file. It seems that changing the status of the 2 rows are not working properly. The one on the last row with the date of 6/1/2021, when I change it to approve, the row that changed was the 1st row with 4/2/2021 date. Same thing with the 2nd row, if I change it to Denied, the 3rd row is the one changing.

    I've added a ComboBox to select Status from. You can select Approved or Denied


    1. You don't need a second UserForm.

    2. I would keep all requests on one sheet. It's much easier to maintain the requests that way. What if someone wants to cancel the existing request?

    For #2, I wanted another sheet for the denied request because on the employee/users perspective, they will be limited for viewing and they won't have the ability to edit anything on this file. Their request will come from an MS Form and will be extracted on a separate excel file. We will then transfer their requests into this file for approval/denial. With the denial sheet, it would give them 2 options to view the requests: on the Calendar (for approved) and on the denial sheet for the denied requests.


    To cancel an existing requests, that's a good one, actually. I never really thought of that. Maybe we can also add anothe cmd button as a Cancel button and of course change its status to cancel. How did I even forget about that. LOL

    I am attaching an update file here. And a few changes on what I am looking to happen here.


    1. If a request is denied, a new userform will come in (which I already created) and admin needs to select a denial reason. Once denied, instead of staying on the same sheet, if you can move it please on another sheet which is the Denied List. Thus, removing it on the Status sheet.


    2. Approved requests will stay under the Status sheet. I have a request, however, which may be too much. But, please let me know if possible. On the Main tab, you can see a sort-of table (L11:M18). On this table, I want to see the approved requests appear based on the requestes dates. Column L will show the Leave Type and column M will show the Name of the employee. I know its a bit of a stretch here, though, I'm hoping it is possible.


    3. I am still looking forward for the ability to amend the status of the request. But, since I want the denied request moved to another sheet, its okay if I can only ammend it from Approved to Denied. And the ability to filter.


    Thank you so much! Stay safe everyone!

    Files

    • Draft 4.xlsm

      (84.7 kB, downloaded 50 times, last: )

    just finished testing the file and I thought it was great what you did here. I do have a few notes I wanna run by you here.


    1. As you can see, after approving/rejecting the requests from the listbox, the same request stays at the list - which becomes prone to duplicate entries. To fix this, I thought of 2 things: (1) I added a new column to the list which shows the status of the request. This way my admins can see that the request already has a status. Or, (2) add a filter to the list which will only show the entries/requests that do not have any status yet. With my knowledge, I've only done the (1) part. I prefer the (2) solution, though, if you can help me with that. I think that's more efficient.


    2. Is there a way that allow admin to change the status of the request? On the mentioned filter above, it would allow me to filter what's shown on the list using the status. I'm wondering if its possible to change the status of the selected request from its initial status to Approve or Reject.



    Thank you for taking the time on answering this.

    Actually, you're right. Scratch the 2 sheet. i am keeping the Status sheet and remove the raw sheet. So entries from MS Form will go to the status sheet, will appear on the listbox and allow admins to approve or deny the request. Then the status will appear on the status column and approved/denial date will appear on the Approved/Denied date column. Please help me how to make that happen.


    I'm so sorry. Its phase 1 of the project, still working on how things are arranged.

    The reason I habe two sheets is because on the raw sheet, these are entries from an MS form automatically directed after users fill up the form. This same sheet will only be available to "admin" of the page. The status sheet along with the sheet with the calendar in it will be visible to admins and "users".


    The end goal of the project is, after the request were approved or rejected, the approved leaves should appear on the calendar. I thought its more efficient if I would a separate sheet for the raw data and a different sheet with the status of the requests.

    Hello All. I need help with this little project.


    As you can see on sheet5, Request Raw, a list of employee data and their requested date. If you open the userform via the "Check Leaves" button, it will display a listbox with the same data on sheet5. Below the listbox are 2 cmdbtns - Approve, Reject. What we want to happen is, a user will select an entry on the listbox. After selecting, they will click either Approve or Reject. If Approve/Rejected, I want the entry to be transferred from Request Raw to "Status" sheet. This sheet is almost the same as the other one with the addition of Status and Approved Date column. Status column will show the status if Approve or Rejected. Approved date will show the date approved which is the date showed in the listbox besides the cmdbuttons. I say "transferred" above because once status is selected, I want it removed from the listbox. Thus, removing it also on the Request Raw sheet.


    Thank you in advance for the help!

    Files

    • Draft 2.xlsm

      (41.6 kB, downloaded 52 times, last: )

    Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post. We are here to help so help us help you!


    Read this to understand why we ask you to do this


    Crossposting

    Thank you for the reminder, I didn't realize it's a thing. Will follow this moving forward.

    MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    Hello everyone. It's nice to be back here. Obviously, I need a big help from this forum, again.


    I am to develop a project that would allow may co-team mates to request for Vacations Leaves via Excel (using a Data Entry form). These request will go directly to a database in Access. Why Access? I thought storing a large amount of "request" in Excel would only increase the file size and make it harder for the codes to execute. If I am wrong here, please educate me a better way of resolving that issue.


    With that being said, the file will be shared AND/OR the users can download the file and save a copy of it on their own desktop. Now, since the requests are in a Access database, again please correct me on this part if I have a misconception, the file, even though it has multiple copies outside and shared to different users, it would update real time on the users end. Am I wrong here? Let me know.


    Another dilemma I have is where to save the database that would allow the Excel files to update real-time. I was thinking of shared drive or folder. Is that a possibility? How about One Drive? Can it do the work?


    The project right now is in a planning phase. I want to sort out this questions first before I start doing it and I think this is the best place to ask for guidance.

    If it helps, I am using an Office 365.