Staffing database template

But when I double click on the name I want from the list box, the only name that populates my form is the first name in my database. So if I double click on a name beginning with "g" the form will populate with the name that is in "A1" on my spreadsheet.

This happens regardless of which name I choose. Please advise. Hi Ric, If you would like to contact me from the contact form on this website and supply all of the requested information. I will see if I can help you. Hi Trevor, Can you mail me the above vb excel file? I will be very happy if you does so as I don't want any kind of modification in it. Your file has all I want. My email id is sumeetshaw21 gmail. I want assistance for creating User form with the specific fields like Date on the top.

I will be waiting for your reply. If you can provide me any email id. I can forward template example. Thanks in advance! However, for the Delete button instead of deleting the employees data, I want send the details of the employees to the archive sheet as I might need it on the future.

Hi Trevor im trying to run the first code to set my add button but im getting an compile error that "invalid use of the Me keyword. Please help. To put the code in the userform. Double quick the userform and out it in there.

I am very new to VBA so would appreciate a little more understanding of the "offset 0, -3 "… see below. Offset 0, -3 ". Hi Scott, offset 0,-3 means offset move to 0 rows stay in the same row -3 means go back 3 columns. One final question — I would like to add a date box on the form, but I need the date to be in the UK format. Is this possible, and where would I put this in the code? Fantastic UserForm. Everything worked first time.

The only thing I changed was the Payroll ID, to be generated automatically. The code below works, it extracts letters from the left or right of name, adds a P to the beginning, and converts the letters from the name to numbers.

Can you tell me where you added the self generating number code. I would like to implement it in my database. Invalid property value" Any suggestions? I think the code would be in this instance. First I clear listbox source , as I have set up rowsource in userform initialize procedure as range.

Then add source and column count Still no joy:. Find txtOrder. Value ListBox1. List ListBox1. Offset 0, 1 ListBox1. Offset 0, 2 ListBox1. Offset 0, 3 ListBox1. Offset 0, 4 ListBox1. Offset 0, 5 ListBox1. Offset 0, 6 ListBox1. Offset 0, 7 ListBox1. Offset 0, 8 ListBox1. Offset 0, 9. Hi Greg, I would need to look at the userform and full code.

I am not sure why Rowsource is in the code above. This Listbox is not populated by Rowsourse if I recall correctly. It is possible that is error is not due to column count.

It may be in your code or a range problem. Hi Greg, The additem method which is for adding rows has a limitation of 10 columns for the rows.

I can have a quick look for you. Recently beginning to learn VBA and loving it : , mostly due to so many generous people like yourself that share great material. This has helped me so much. Thank you again for such an excellent post. Dear Trevor, really thank you for the great work which help me in my work i modifiy the worksheet as i change thePayroll ID to Corporation number for the staff.

How i can make the search by Corporation number not by the name in the userform. G'Day Trevor, Unbelievable work so helpful and generous with your time and expertise, Thanks alot Chris.

Hi Eyad, In this tutorial I use the find method to locate the items and loop through them and add them one row at a time into the Listbox using AddItem. If you change the the search in the Listbox other macros will needed to be adjusted as well. Can be done but is not a simple process. There are other tutorials that populate list box with named ranges by the Rowsource that would be easier to change around.

In those tutorials we run an advanced filter, the criteria is easier to change. Hi sir I have problem with sub lookup Me. Hi Omar, Me. Means "this userform". It can only be used with in the userform.

If it is saying Invalid use it is probably because this code is in a module and not the userform. Thank you very much for putting this together! Everything worked great the second time around. I'm a beginner with VBA.

So, I can't believe I was able to apply your code with some modifications to my school's fundraising project. I'm incredibly elated! Greetings from Ireland Trevor, I'm doing an internship with a company and your site has proven amazing and got me out of alot of holes as I dont have a programming background.

I wonder is there an easy way to get the infomation thats been put into the cells to come up in capital letters?? Thanks so much This is the template I have been using off your site.

I modifying it now to reduce difficulties of my small team on administering approx persons. God bless you. Question: What code would I use or replace to have the "Delete" button serve as an "Archive" function? I would like to archive employee records rather than deleting them. Any help would be great. Hi Ezra, 1.

Add another sheet for the archiving. This will be the 4th sheet. Add the headers from column C as in the data sheet 3. Add a command button to the userform and call it cmdArchive 4. Add this code This will give you the ability to archive any staff member.

Not the sheet code name is "Sheet4" this is not the sheet name. Range "L:L". Range "A1:J1". Copy nextrow. Thanks for the prompt response! I edited what you provided and made it work. I have another request, if you can kindly provide your feedback. I am trying to apply to more forms within the main reg form. What do I need to changed or replace?

I'm stuck : Any help would be appreciated. Hi Ezra, did you ever find an answer to this? I have been through this twice and have run into the same error twice! Thank you for all your help — I created a database similar to the Staff Database. All buttons work perfectly except the double click command. I get an error saying "An Error has occurred — The Error number is: Object Variable or with block variable not set yet.

Please notify administrator. Here's the code I used from the Staff Database. Range "G:G". Similar to what you have the name concatenating in the staff database. Hi Willaim, It is probably the "findvalue" variable I have added some comments at the end of the article that may help. Modifying the Userform Added 30th October I have added this short section because many are having trouble with moving the columns for looking up staff and editing them.

So we are looking up Reg3 to find all of the staff that starts with our search criteria Here is the code in part that populates the listbox.

Note E:E Reg3. Sub Lookup With Sheet2. We use Reg4 because it is our unique ID Note this variable. So we offset back to the start. As always thank you appreciate the quick reply — Problem solved — it was the findvalue variable — needed to offset it one more column over. I would send the values to the worksheet using the change event for each control and then return the calculated vale back to the userform to add to the data set.

This will solve a lot of formatting problems. The change event for column L day's left on the user form would be — Me. I tried this and got an error. And dragged it all the way down referencing rows an empty column K cells in anticipating of new contracts Problem with this approach is that when it comes time to add a new contract to the database on the user form, the nextrow function in my code will notice a value of the simple dragged down formula and offset to the row Hi William, Use a few cells on the worksheet to do the calculation.

Here is some code from another project staff database that does that that may help you. Cells O7 and P7 have the date sent to them from the change event below. Cell Q7 has the result highlighted. The same cells are just used over and over with new info.

Range "O7". Range "P7". Value End With Me. Range "Q7". This will give a clearer calculation then something like this. Value — Me. Value which will be problematic with regional date formats. Happy new year to you Trevor and wishing you good health and lots more exciting projects for I have successfully loaded up this data registration form and amazed how I was able to get it running with the minimum of fuss using 40 Reg.!!! Is it possible to to use the value of two or more reg and either multiply, add or divide and place the result in another reg.

I would very much appreciate it if you could assist me in this matter. Should I be declaring variables? Hi Sara, There are 2 ways I use to do this. Send the values to the sheet and use formulas, then return the result to the userform. Calculate on the userform. I would only use this method for very simple calculations and not with dates. Hi trev, hope you are well. Just a short email to say I haven't had the chance to move forward from this point due to work commitments..

All the best until then Sara. Apologies for maybe asking silly question.. But your tutorials are amazingly simple and brilliant. Think my husband thinks I've become a bit crazy over these forms …. This is great. Thank you. I purchased the completed project and I am trying to modify it for my own needs.

When I try to unprotect a sheet it prompts me for a password. What is the correct password to unportect it? Hi Trevor, Great job. Thank you very much. I adjusted it for my own needs. My name is John and i work to a hospital will one thousand employees.

I have been developing excel systems for the hospital but not anything near what you develop. Last week the HR director called me and requested me to develop a database with an userform interface where data will be entered. I have no idea where to start but i told him i will look into it.

Will you mind to help me. I have tried the above steps and am coming up with something but it seem like it going to take me too long to come up with something concrete and good for these kind of an organization. Hi John, You may find a tutorial that is suitable. My schedule and resources do not permit me to help you.

I can not add name ranges in combo box. While adding the "registration in row source for Reg 15, an error is will come. I cannot set rowsources in comb box. Hi Renju, Invalid property means that the value you added to the recourse is invalid. If it is a named range you should check that the range is valid and working. Hi Trevor. A quick question. I want to modify this data base. The search feature can this be adapted to be like the phone book where you have a drop down box and search different names and phone numbers etc?

I know you can't offer help on this as it's a modification but it it possible by using the same principles? Hi Mike, At the moment the app looks up Staff Department and a variable of the due bate. They are not mutually inclusive so will work separately.

The principle is the same as the Phone in as much as both use Advance filters to search. Thanks for offering to help John Kuriah. If you need to have you email comment removed let me know. The world is full of spam sadly. Hi Trevor, this has been very helpful in both teaching VBA and userforms. I have ran into a problem now that I have modified the file however.

In your directions you tell us to place the lookup macro in a module. When I do so I get a compile error "invalid use of a me key word". I then moved the lookup macro into my userform as you specified in previous comments and get the following, error "could not set the list property.

Invalid list property array. Any thoughts on where the lookup code should be placed and why I get error ? Code below. Clear 'look up parts or all of full mname With Sheet1. Offset 0, 5. There is additional information at the end of the modification tutorial about changing the lookup column. Hi I have implemented your userform in my application, working great, thanks.

I would like to implement a second userform on a different sheet in the same application, I've exported and imported the first userform, what changes will I have to make to the second set of procedures, other than the control names. Do I rename the procedure names? Will cNum and X have to be renamed throughout the code? Hi Al, It is probably best to rename the procedures. It will help identify them with your error handling. You can just simply put the letter 2 after the procedure or use a descriptive name.

I think that cNum and X should be able to stay the same. As I am not exactly sure what you are doing and I suggest that you take a copy of your file and then make one change at a time, test the file and backup before the next change. It sounds like like you are developing an awesome app. If I do a search for c in the form I will get a correct listing of matches.

If I do a double click lookup of John Cash I get Jim Abbot entered into the controls, even though Jim Abbot is not even part to the found names, Lisa Count enters Al Logan into the control settings, the other 9 or so names in the lstLookups enter the data correctly data into the controls, only Cash an d Count are incorrect.

A second question, if I were to type the letter m in the textLookup box I will get the header labels included in the List Box, how can I exclude the header labels from the search? Hi Al, I appreciate you have modified the original application so can only offer general comments. The lookup is using an advanced filter the results of which are populating the the listbox rowsourse.

As shown in the tutorials. The double click is using the find function on a specific column of unique ID's. It finds the row and then populates the controls with vlaues. You can set the properties for the listbox to not show headers or it may be that the named range is including the headers.

Data didn't line up, this is the data in the database. Don't expect you to debug the code, but though you might have a quick idea of what could be problem. The cPayroll is showing the correct unique ID, but for 2 records the find is not returning the correct values in the findvalue variable.

There are only 12 records in my test db. The other records in the list box are returning the proper values. When I change it to xlWhole I get "error has occurred…Error 9…subscript out of range" When you say different data type do you mean numeric vs.

Hi Al, I thought you might be running the code with a variable that was not set from the listbox. I am not sure what changes you have made did notice some blanks in the data you showed earlier and the offset reference change and the column change from L to F. You should be able to use the code that works and test from there. I don't think I can help you further without looking at the file. If you want to set up a small test file that shows the problem I would happy to have a quick look.

It seems like things work if I use a text ID, don;t understand the problem, but text should be okay. Does something have to be set to concatenate two controls, can't concatenate two controls. But i am stuck on the double click event.

Hi Trevor, I've just finished Part 3 of this brilliant tutorial and I'm looking for the next part — creating the interface. Is there a video on this, if so could I have the linl please. Hi Roy, Here is the link to the tutorial for the Interface and also for modifying the application. Thanks for this awesome information it really guided a noob like myself to understanding vba and userforms to a certain extent. When I open the user form I receive the following compile error: method or data member not found.

It also refers me to this code : Me. Would you kindly assist. As far as I'm aware there is no worksheet code in this tutorial. The user form is activated from a shape on the worksheet that runs a macro to show the user form. Are you doing the project unmodified and using the template or are you changing around to suit your needs? Is this possible and if so were would I look.

Thanks for the site I have learnt a lot in the few days I have used it. Got to keep this old brain ticking. Hi Bob, It is possible. Unfortunately my time and resources do not permit me to modify file. As you gain more experience these changes can be made. Thanks Trev I have worked out a few things and am trying them, new userform etc.

It is fun learning so time well spent. Thanks Bob. I have managed to work on a file which have 70 columns and it is working perfectly. I have also added a combobox where i have to select a department before submitting the data. I have also added another button for combining all the departments staff to one sheet for all staff. The challenge is, if the staff is entered twice by mistake, when i enter combine i get duplicates in my combined staff sheet. Which code can i use together with combine code to avoid duplicates?

Another question is, when a staff resigns, instead of deleting them, i would like them to be moved from the other sheets and pasted in a sheet named Termination without leaving a copy in all the other sheet.

If WorksheetFunction. If you want to send the terminated staff member to another sheet then you would need to copy the data before deleting and paste into sheet in the next available row of that sheet.. I really like the application and trying to modify it slightly. I am familiar with VBA but am confused about two aspects of your code, specifically when you add data to the table.

First Issue: On your original data table, you had the table start on column c. Column a and b were empty. I modified it to start on column b. But for some reason, when I input data, the data starts on column c. I can't see it. Second issue: after i input one data and add to table and then i go and try to input another data, the next row starts at row 3.

I think there is an issue with identifying next row. I figured out the first issue. Offset 1, 0 to. Your Tutorials are great. When I was watching your tutorials I was really surprised. My Requirement is I have a lot of data in my main sheet and i want specific columns required to copy and paste automatically in different sheets as per my requirements with out any duplicates and when i update data in the Main Sheet automatically the other sheets should be updated.

From Main sheet i want specific columns required to copy and paste automatically in different sheets depends on sheet requirement columns that is Your templates and instructions are extremely helpful. I have a question on the user form you discuss in your tutorial above. I'm working on a similar database where the only difference is that the entry form and the list form are separate in separate windows. Is there a way to open pass a list item from the list form into the entry form on double-click?

Thanks in advance. Hi Lachezar, I have sent a file to your email address that shows how to transfer values between user forms. You may be able to use the code and therein and adapted to your needs. Please accept my best wishes Trevor. Can u please help me? Hi Ahmad, I can offer you some help.

But you are not supplied me with enough information to do so. There is a contact page on the website if you are requesting support please use that page and supply all the information requested. If I'm able to I will endeavour to help you with whatever problems you are having as my time permits. I am able to add data to excel from. Caption also. Please support for adding labels to database. Hi Mayank, Labels are there as descriptions for the controls.

They should not contain data that needs to be saved. The labels should be the same as the headers in the database sheet. Hi Trev, Thanks for your assistance, thus far. I decided to delete the workbook, so I could then download the workbook a second time. I followed all the instructions and have not made any amendments to the workbook at all. I also copied and pasted the codes as per the instructions.

Good news is I can now edit data, but when I conduct any subsequent lookups, I am now getting the following error:. Invalid property value". Hi Steve, You have not given me enough information to help. What procedure are you referring to? Comment out the error handler in the procedure and post the line of code that shows the error and the error message.

Or contact me by the contact form. I've just realised that I created Listboxes as opposed to Comboboxes…meaning theses boxes weren't reseting to blank. Could this have caused the error? In each procedure you will notice a line of code toward the top. On Error goto …. Put an apostrophe ' in front of this line to comment out the error handler.

Run the code and when error occurs press debug. You should be taken to the offending line of code. Also note the error message. Hi Trev, Just as I suspected, the Reset Button was unable to clear the data in the Listbox; but now that I've replaced those Listboxes with Comboboxes, the error has gone away.

Regards, Steve. Just wondering, how hard is it to refine the lookup function by introducing a date search txtbox in conjunction with Column E:E? Have you ever thought of doing a customer service log similar to this but the lookup is searching from a customer list to fill certain parts of the form and when you hit the cmdAdd the entire info is logged into a complaint database?

In this way, the firm will not incur losses as a result of wastage of time. By using this plan template in Word , you will be able to determine which positions are needed for your restaurant and what are the qualities to look for in each position.

Prepare valid questions for the interview and mention how you plan to train new hires along with their incentive details by editing and modifying this template in any file formats. The best part of downloading our sample plan example template is that it is fully compatible with different editing applications and can be downloaded in any of your devices! The easiest way to make this is by using this Word template that has been professionally designed.

You only have to fill in the information based on your needs as the template has the layout to guide you. This helps in managing skills, and personnel for every need for the organization at all times. Use this PDF template and prepare a professional-looking plan that covers all staff needs of the organization and to help you avoid surprises and unnecessary downtime.

Download this free Excel template today and stay on top of staff management at all times. It lists all processes and activities required for the effective management of the staff.

By using this PDF template, you can make the plan to develop the skills and career of the staff in your organization without missing any important aspect. Additionally, by creating the monthly work schedules at the beginning of each year, you will have a comprehensive view of the year and be able to anticipate what is coming down the line.

With this simple monthly work schedule template, you can set up individual tabs for each month, input important events into the calendar, and add notes to provide details for each events.

No matter the size or nature of your business, having a weekly work schedule will help keep your employees aware of the weekly tasks and time associated with completing each task. Keeping a schedule of these items will help your team remain on track and ensure that the work is completed on time. Use a weekly work schedule template to input weekly tasks and organize them by day of week and time of day.

Gone are the days where employees would manually clock in and out. Additionally, asking employees to keep a daily timesheet log can be helpful to track daily activities. This employee timesheet template will enable you to create one file for each of your employees and track his or her time on a daily, weekly, bi-weekly, and monthly basis. Additionally, the pre-built formulas included quickly calculate totals to help streamline employee payroll processing. Creating a work schedule for your team keeps everyone on the same page.

It will allow you to ensure that you have coverage when needed, and let your team know when they are scheduled to work. This work schedule template streamlines this process and provides a consistent view for your team. Input employee names for the times and dates they will work, and create a rolling history of previous weeks by adding a new tab for each week. Depending on how many employees you have, payroll processing can be an arduous task. With a simple payroll template, you can easily add employee details, salaries, overtime, exemptions and more, for each payroll period.

Also, by creating a tab for each payroll period, you can keep a rolling history of payroll throughout the month, quarter and year. Empower your people to go above and beyond with a flexible platform designed to match the needs of your team — and adapt as those needs change.

The Smartsheet platform makes it easy to plan, capture, manage, and report on work from anywhere, helping your team be more effective and get more done. Report on key metrics and get real-time visibility into work as it happens with roll-up reports, dashboards, and automated workflows built to keep your team connected and informed. Try Smartsheet for free, today. In This Article. Employee Attendance Tracker. Keep your employees connected, informed, and engaged with Smartsheet. Employee Training Plan.

Vacation Schedule. Candidate Screening Tracker. New Hire Checklist. Staffing or Recruiting Plan.