IF Statements for Macro Buttons?

[BravoLimaKilo]

Hello all,

I was wondering if I can get some input/advice on how to proceed with the project that I am currently tasked with.

Attached is just a rough template of what I envision the finished product would look like.

I would like to create an Excel file that:

1) Allows me to use my drop down menu in the "Input" spreadsheet to select a student, and either "approve" or "reject" a specific student's request for a course.

2) Once I either click "approve" or "reject", Excel will automatically populate my decision into the corresponding spreadsheet of "math", "science", etc.

3) The first time I reject a student, it will correspond to a specific color (as indicated in the "math" or "science" spreadsheet). There will be three different colors I use to quickly assist me in determining how many times I have rejected a specific student's request for a course.

4) I suppose that using the Macro, Excel will need to determine IF this is the first time I reject the student, it will return a specific color. IF I have rejected the student for a second time, it will return another color, etc.

Moreover, I will need the Macro to understand that IF a specific student (ex. John SMITH) is already in a certain spreadsheet (ex. Math), it will recognize that and instead of inserting a new row, it will merely add the corresponding color to him.

IF the student is not on a certain spreadsheet, Macro will simply add him into it in a new row.

Is this possible? In my mind, it doesn't seem too complicated but there are a lot of IF statements that may complicate things/possibly make this project not possible.

Thank you.

[KOKOSEK]

Subs to assign into related (as named) checkboxes (but do not just paste into your file due to few changes on layout):

Please Login or Register  to view this content.

Check attached file. Should be fine.

[BravoLimaKilo]

Hey KOKOSEK,

You are awesome! Thank you so much. This really helped me to kick-start the project and gave me an idea on how this can work!

Question: Is there any way we can make it so EVERYTIME a "decision" has been clicked in the "input" spreadsheet, that it creates a new column in the corresponding subject? Of course, this is given the fact that the student has already been "inputted" before, and thus, a new column can just be added.

If a student has never been "inputted", then a new row (which is what you have done in your example) will be added.

Reason being is so if the principal looks at the spreadsheet, he can easily tell chronologically when the 1st reject, 2nd reject, and 3rd reject etc were.

This can assist the principal in seeing that if a certain student has been rejected a course 3+ times, he will know that in fairness to the student, he will try his best to put him in the next course, hopefully resulting in an "approval".

The format of what I came up with is attached.

Thank you!

[KOKOSEK]

Do you want to see that for ex. after 2 rejection, approval been at 3rd attempt? Not just actual state.
I do not understand in example2 why there are 3 approvals? If course if approved, it's approved. I get idea about consecutive rejections then aproval but why there are 4 approvals?

[KOKOSEK]

Should works as you want to:

Capture.JPG

[BravoLimaKilo]

Thank you! That is exactly what I am looking for.

I have been messing around with the VBA code, and have been able to make slight alterations; however, I am trying to make it so whenever it is "rejected" or "approved", I want it to not only display the corresponding color, but to have the word "REJECTED" or "APPROVED".

So far, not much luck.

I BELIEVE it has something to do with the code below, but whenever I change it, nothing happens and messes up any corresponding rejects afterwards.

Please Login or Register  to view this content.

Secondly, I want to have it so whenever a certain student has already been approved, that whenever someone tries to approve them again in the "input" spreadsheet, a popup will come up stating that the student has already been approved.

Lastly, how come there are borders whenever I "approve" or "reject" a certain student that appears in the "math" spreadsheet? I love how it is done, but I cannot for the life of me figure out why that is. I do not see in the code anywhere regarding borders. I am trying to get the same thing done in my own Excel, but nothing is coming up...

Could anyone push me in the right direction regarding this?

[KOKOSEK]

Problem is that if you want to display word 'reject' or 'approve' now, code has to be change completely. I'll take a look on it.
What do you mean by 'borders'? Do you mean bordered cells? It is set in conditional formatting.
Of course to create sheet with another course, just copy math tab and rename it. Be sure that name is exact as on your list.

EDIT: check attached file. With a words 'reject/approve' and with check of approval.

[BravoLimaKilo]

Hey Kokosek,

You are absolutely awesome. Thank you so much for your help. There's still some minor tinkering I need to do, so hopefully it won't be too hard for me, haha.

THANK YOU!

Edit: Is there any way to easily "delete" a certain student in the "Math" spreadsheet in the event that a teacher accidentally selects the wrong student in the "input" page and selects approve or reject?

I was thinking of a "delete" button in the "math" spreadsheet using macros, but I can only make it so it deletes a FIXED column/row. However, since we do not know how many students there will be in the future in the "math" spreadsheet, it is hard to create a button for every column/row.

Is there any way to have Excel recognize that clicking "delete" in the "Math" spreadsheet will delete the exact student that I want, regardless of which column/row they are in?

[KOKOSEK]

Doable of course. 'Misclicks' happened.
Small button on right of student's name to remove his line from register.

Check attached file.

[BravoLimaKilo]

Thank you, Kokosek!

Is there any way to change the date format to, for example, "February 1, 2020"?

I see that the date formation is currently "dd mmmm yy".

I tried replacing that with "mmmm d, yyyy", but it does not seem to be working. Am I doing something wrong?

Please Login or Register  to view this content.

[KOKOSEK]

As You wish:

Capture.JPG

Changed in attached file.

[BravoLimaKilo]

Hey Kokosek,

Thank you for your help.

Everything seems to be working great with the Excel, but I thought maybe after "approving" or "rejecting" a student, the input section respective of that would be blank, to avoid confusion with staff and over-clicking.

Would this be a complete rewrite of the code or just a simple add-on to it?

[KOKOSEK]

What do you mean? After approve you can't do anything more. Can't be rejected after approval or approve after approval.

[BravoLimaKilo]

Hey Kokosek,

Nothing changes, but I wanted it so after clicking approve/reject in the input page, the row will go blank for that student in the input page.

[KOKOSEK]

Change end of each macro like below:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

[BravoLimaKilo]

Hey Kokosek,

Thanks!

I made it so A3(Student), B3(Course), and E3(Today's Date) cleared every time a decision was clicked!

I have been playing with the Excel/Macros and was wondering if it would be possible for a MsgBox to come up if either A3, B3, or E3 were left blank? For instance, "error: StName was left blank" or "error: ExamDate was left blank"?

I could see the possibility that people would forget to fill out the required fields, which could cause issues with incomplete fields in the respective courses.

Thank you!

[KOKOSEK]

Of course it is possible.

Check attached file.

[BravoLimaKilo]

Hey Kokosek,

Thank you!

I am just trying to understand the code and learn more about coding...

I got it to work on my version of the Excel; however, is there a reason why I cannot have that as Module 3, which is separate from the reject/approve coding? It seems that whenever I place the new code into Module 3, the reject/approve button does not work. However, it does respond correctly to missing fields.

It is not a big deal, and I do not want to change anything. I am just trying to understand the reason behind this.

Thank you!

[KOKOSEK]

There is var which works inside Module, it a probably reason. I have no idea what you want to do.

[BravoLimaKilo]

There is var which works inside Module, it a probably reason. I have no idea what you want to do.

Thanks for clarifying. I was just curious, and wanted to know. Thank you!