Data Validation Of Names Required from given List :confused

[pipsmultan]

Greetings to all

I am running into big problem

I have two sheets called “Timetable” & “Teachers Name List”

What I want is a “VB/Macro Code” which can check and validates names into my “Timetable” sheet from “Teachers Name List” sheet

So that if any one enter wrong spelling of any teachers in “Timetable” sheet, it Prevent from entering it and give error message “Spelling mismatch from Teachers Name List sheet”

Excel file & Image are attached

Time table high school.xls

Teachers name Validation Sample.jpg

Please make the code flexible so that I may able to Add and Delete name from the “Teachers Name List” anytime in future

And good Help from any one will be highly appreciated

Many thanks

[pipsmultan]

any one out there who can help me on the above issue.. please

many thanks in advance

[pipsmultan]

Anyone "kind" assistance would be very much appreciated....

[pipsmultan]

Bump no response

[p24leclerc]

Ok, here is a solution to your problem.
Look at the attached workbook. You'll see that I did modify your Teachers Name List by adding a column named Teachers number. This is what the users must enter in your Timetable cells when creating the schedule. Each time a cell is edited, the following macro will search the cell for the word "Teacher" and it will replace it with the teacher name. You can have as many teachers as you want in a cell. You just HAVE TO separate them with a SPACE.
All you have to do is make sure there is a space before and after the teacher's number.

Here is the macro:

Please Login or Register  to view this content.

The workbook is ready to run.

[pipsmultan]

The Ideal you use is very good

1. But problem is I’m not the only one who entering the data in timetable sheet, as it is hard to remember all the "Teachers number" of a particular teachers rather than their names. It will not look user friendly to others.

2. I have uploaded the some error screen images which appears on wrong entry which halt the excel file to stop responding and it will not work unless and until I close excel file and reopen it.

01.jpg

02.jpg


I was using the following formula for name Validation in “Data tab”. But it is only possible if I can afford not to have merged cell and can enter names in cells.

Please Login or Register  to view this content.

This will capture teacher's names in A7 to A100. If I have more teachers in future, and In the Error Alter tab, I put appropriate alert.

but it is not working for me I want to know is it possible to convert above function in "VB/Macro"

Or any other alternate will be highly appreciated

Many Thanks

[p24leclerc]

The error you got is part of the macro I programmed for you. You click OK and it will resume normal operations.
What you must enter in you Timetable cell is something like this:
Physics Teacher12 10th west
And the macro changes this to:
Physics Mis Ema 10th west

I thought it would be possible to print the teachers list so people entering a schedule could easily find the teachers number and may be get used to it and know it all by heart.
Sorry if this is not a possible solution to your problem.

[pipsmultan]

Sorry if this is not a possible solution to your problem.

No not at all.
I respect your thought and your Hard Work u have put in, I will always be grateful for all that you have done to help me.
i still working on it to find the best possible alternate solution which can check and validates names into my “Timetable” sheet from “Teachers Name List” sheet.

and if You Or Anyone find any alternate plz share

I am so thankful for the time you took to help with my project.

Regards

[p24leclerc]

here is another possible solution, probably more acceptable.

First, I created a named range in Teachers Name List that will expand as you add new teachers below the actual list. I would recomment not to change tis location unless you know about named range and you make sure it's still connected to the proper range of data.

You'll see that I created a button on the top left of the screen named "New course" I did not know what exact terminology you wanted to use so you can change those as you want. I also froze the scroll of the screen so this button is always visible.

Now here is how it works:
1. you click on any cell where you want to create a new course. You set it as you wish regarding the merged cells prior to click on the button.
2. you click on the button which will open a userform divided in 3 sections (I think that is what you needed by looking at actual schedule)
First part is the matter of the course. You write directly in the texbox (like Math or Bio or Physics)
Second part is the teachers' selection. it is a listbox that allow multiple selection. You hold the CTRL key and click on as many teachers as you like.
Thrid part is the location of the course (my understanging of it, though). You write directly into the textbox.
3. when you are done, you click on the left button at the bottom to transfer all the information into the selected cell. The macro will add a line feed between each section.

And that's about it. The other button at the bottom is self explanatory.

Hope this will help you and your co-workers

[p24leclerc]

see my previous post just above.

[pipsmultan]

see my previous post just above.

Hi,

i m glad to see You

plz wait let me check your previous post

[pipsmultan]

This is superb! You are so creative—I always love getting your perspective on Projects, this is more than I expected.
I had no idea a Timetable could look this good.

You set a high bar with this one I really appreciate your help, Thanks a million for finding outstanding solution of problems

1. Just need little bit update in New Course "Dialogue Box" & "Teachers Name List" please see sample image below

Attachment 334542

Attachment 334543

2. I dont know why i am getting error in "Teachers Workload" Sheet, plz see the error image

Attachment 334544

3. last why i cant do "Undo/Redo" or Ctrl+Z / Ctrl+Y in Sheet, is there any solution of it.

Many Many Thanks for all your Superb Effort

Regards

[p24leclerc]

Hi, everything is running well on my computer. I can't see what the problem could be.

About your attachements, I just cannot open them; none of them.

There is no way to undo what a macro does. That's why the undo is grey.

[pipsmultan]

Hi,

i have attach the excel file and put the sample images inside.

Time table high school-2.xls

hope this will open

Regards

[p24leclerc]

The 2 macros are working as intended. I got no error. Were they working for you too in the actual workbook?

I can see your pictures but I don't understand how it relates to what there is in the workbook.
You seems to have made some changes to the sheets ( as per pictures) but I see nothing changed in the workbook itself.
I don't understand your HIGHER, MIDDLE and JUNIOR teacher's list either.
Can you attached a modified workbook and tell me how it is supposed to be working?
I feel like I'm working on something that is not really what you need.
I'm lost. Not sure where you are going from here.

[pipsmultan]

For your Clear understanding i have Highlighted Some Cell in "Timetable" Sheet which is showing HIGHER, MIDDLE and JUNIOR Section or your can say Classes
for example
Green Color is Showing "Higher Section"
Yellow Color is Showing "Middle Section"
Blue Color is Showing Junior Section

now actually what i trying to say is i hav made some changes in the "New Course" Button. When you click on the "New Course" button a "Dialogue box" in which it showing HIGHER, MIDDLE and JUNIOR Section Teachers name List

"New Course" Button which will open a userform which was divided in 3 sections, But now it is diived in 5 Section.

Second part is the teachers' selection. it is a 1 listbox that allow multiple selection, Now i have added 2 more listbox with their name (You can see their captions).

U have created a named range in Teachers Name List that will expand as I add new teachers below the actual list. Now i have added 2 More "Columns" and i renamed it (HIGHER, MIDDLE and JUNIOR Section Teachers List) as you can see in "Teachers Name List" Sheet

Reason of doing this to Categorized teachers according to there respective department/Sections, So that respective Department teacher can be easily Selected & Searchable by anyone

last all 3 listbox should be interconnected with each other have that allow multiple selection "like if any one want ONE/Two teacher from Middle Section and One/Two Teacher From "Junior Section" they all can be adjusted in one Cell and soo on


Time table high school-2.xls
hope u Understand Now

Best Regards

[p24leclerc]

Look at the attached workbook.
I created conditional formatting in sheets Teachers Name List and Teachers Workload so that new entry will automatically have borders around it.

I created new named range for Middle and Junior teacher's section.

Timetable macro "New course" waa modified to be able to select as many teachers as you want from any teacher's section.

Try it, I hope it will be work as you expected.

There is a macro in the Teachers Workload section of VBE (Visual Basix Editor) that is erasing all cells of the workload sheet if any cell is modified. I did this so that no one can change this table so that it does not represent the Timetable content anymore. If you don't like this, just erase this macro from that sheet. Here is the macro:

Please Login or Register  to view this content.

[pipsmultan]

SUPERBLY DONE

Thank you so much for all of that awesome

Your service has been of incomparable value and I wanted to let you know that thousands of miles away from you, I’ll forever be grateful, I appreciate your critical thinking around this project.

Thank you from the bottom of my heart for your commitment. You are not only a valued member of this FORUM, you’re a dear friend.

last "Thanks a million for your kind Help

Best Regards