Approved/Deny in listbox to another sheet

  • 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 33 times, last: )
  • 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.

  • 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.

  • That's OK. It's good to make suggestions on design as well as code.


    You will need to change the destination sheet in the code.


    I've made a coupler of changes to the code. he raw sheet is now unnecessary

  • 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.

  • Try this, it allows you to amend entries. You can't filter a ListBox, if you really need to I can add some code

  • 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 28 times, last: )
  • 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?

  • 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'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

  • 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.