PDA

View Full Version : Userforms (codes and formulas)



raybot
10-04-2013, 11:32 AM
Lets see if I can explain this correctly.

I am very new to the world of VBA etc, I have worked with spreadsheets for a while now and have never had to look into the more advanced side of things but I took on this project and now I seem to be stuck. I have almost the end product but stuck with the following.

I am in the middle of creating a spreadsheet allocating times for people to attend my office. The day they attend will be determined by the last two digits of their national insurance number. I have made my userform for the information I need to record and put into the spreadsheet (National Insurance Number (NINO for short), Surname, Initials, Date of first contact, Date of Birth). (For those of you who are outside the UK National Insurance Number format is as follows: AB123456A). If the last two digits of their NINO are even then it will be week 1, if they are odd it will be week 2. The day of the week is decided as follows:
Monday - 00 - 19, Tuesday - 20 - 39, Wednesday - 40 - 59, Thursday - 60 - 79 and Friday - 80 - 99.
I have the formula on a separate sheet which works this out as follows:
=MID(cell where NINO is input,7,2)+0 - lets the next line know what the last two digits are
=IF(A15>79,"FRI",IF(A15>59,"THU",IF(A15>39,"WED",IF(A15>19,"TUE", "MON")))) - this determines the day (A15 being the cell where the above formula is being stored at the moment)
=IF(MOD(A15,2), "1", "2") determines the week.
This determined then I have to make a timeslot for 4 different people.
What I need is some way of putting the above formulas into my userform so that when I input peoples data as required once I click on the submit button, it gives the next available timeslot to either person 1, 2, 3 or 4. This can be done automatically but I would prefer to select either 1, 2, 3 or 4 to even out the number of people each of my people see. Also, when I enter the NINO, I need to check if that there is a duplicate therefore stopping me from inputting the same person twice.

Or if there is another way of doing this, I am open to offers!! sensible ones of course.

Can anyone help please?????

SamT
10-04-2013, 05:01 PM
First, I am pretty sure that you are using a Worksheet and not a UserForm, because a UserForm has Code, and a Worksheet has formulas. But, then you have a submit button, so I'm not sure.

Second, I think you are saying that you have four Interviewers in your office and you want to assign visitors, (by NINO,) to each of those four Interviewers equally.

Is that right?

If you are in fact using a Microsoft Forms UserForm, then you should not have any formulas on the Worksheet.

BTW, If you are putting buttons on a Worksheet, I recommend that you DO NOT use the Excel Forms Menu, DO use the Excel Controls Menu. However, unless you start over, might as well use the buttons you've got.

Can you Upload a sample workbook? Use the "Go Advanced" button below the post editor, then the "Manage Attachments" below the advanced post editor.

oldman
10-04-2013, 05:02 PM
Good Lord that's too much information for one paragraph! Break it down to one paragraph for each issue. Please include a copy of your project with sample data.

oldman
10-04-2013, 05:03 PM
First, I am pretty sure that you are using a Worksheet and not a UserForm, because a UserForm has Code, and a Worksheet has formulas. But, then you have a submit button, so I'm not sure.

Second, I think you are saying that you have four Interviewers in your office and you want to assign visitors, (by NINO,) to each of those four Interviewers equally.

Is that right?

If you are in fact using a Microsoft Forms UserForm, then you should not have any formulas on the Worksheet.

BTW, If you are putting buttons on a Worksheet, I recommend that you DO NOT use the Excel Forms Menu, DO use the Excel Controls Menu. However, unless you start over, might as well use the buttons you've got.

Can you Upload a sample workbook? Use the "Go Advanced" button below the post editor, then the "Manage Attachments" below the advanced post editor.

You responded first. . .so you get it.

SamT
10-05-2013, 08:16 AM
@ Oldman,

There are no assigned tutors here. Feel free to jump in any time.

oldman
10-05-2013, 09:52 AM
@ Oldman,

There are no assigned tutors here. Feel free to jump in any time.

All too often I jump in without knowing how deep the water.

raybot
10-05-2013, 02:50 PM
Here is a sample of the spreadsheet with userform. If there is an easier way then I am open to any easy suggestions, remember, I am totally new to vba etc, I can play with formulae but don't know codes, just learning as i go along

SamT
10-05-2013, 07:17 PM
@ OM, That's how they taught to swim in the Navy.

SamT
10-05-2013, 09:39 PM
About the attachment: Appointment sheets have two weeks worth of daily sections. The main book has an appt sht for each interviewer. Each interviewer's Appt books have one Appt Sht.

The Main book also has a Time off sht to record interviewers' days off. It has Three monthly database sheets, (next years' will have 12,) to record the "clients data and attendance records. Finally, it has a Template sheet to make it easy to add new Appt Day sections to all the books.

The main book has an example UserForm. The Appt books don't yet. The Appt Books are stored in a shared folder.

Each morning the interviewers will copy their Appt Book, and each evening they will save it over the one in the Shared folder

Each morning the appt setter will use the Userform to upload the previous day's Data from the Appt Books and replace the Appt books with new ones. During the Day the Appt setter will set apps and in the evening save the main book.

The replacement Appt Books will have the previous day's Appt Section deleted and a new day's section appended so that the Appt Books always have two weeks worth of sections, even if the section(s) is empty.

Edited To add: I'm tired, there are logic errors in the flow.

raybot
10-06-2013, 04:22 AM
Hi SamT
What i suddenly realised is I missed some information out. The interviewers will see the same people on a two weekly cycle so once a person has been added to their appointments sheet, they will stay there until the interviewer does not need to see them again. Also, I see from what you sent me back that you mention database, I'm not attaching this to a database so I am lost ???

SamT
10-06-2013, 08:36 AM
By data base, I meant the monthly sheets, which are used to permanently record all appts made.

They would not work as they are currently configured, because of the biweekly meetings.

The biweekly meetings would not effect the rest of the process as so far stated. A little code can keep the client on the appt sheeets until the I-viewers notify otherwise.

The package I uploaded was not meant to be the solution, just a possibility of ideas for one.

Forgive my keyboard its mistakers. It hasn't hd enough coffee yet. :)

oldman
10-06-2013, 09:00 AM
Coffee flavored keyboards. I have had numerous.