Code to perform task based on data entered in userform

**Staci** · 03-05-2011, 09:32 PM

With the assistance of a few people I was able to get a userform that will help me record employee data. I was wondering if using code I would able to perform another task from the data obtained from the userform.

Just for an example, let say I have 200 employees, and I have to enter their data one by one into this userform. I want to install a way to "check" and make sure I do not forget someone.
So after using the userform, can I have it put the employee information into another sheet.

So if I have the Name List going down in column A, then in Column B would be January 1, in Column C would be January 2, in Column D would be January 3, etc.

Once I enter information in the userform, that enters the data into the rData, is there a way using code to then add the sum of the hour field for that person, and enter it into the worksheet Attendance, to the field that corresponds to the date and name of the person entered. I know I can use a pivot table to get this data, but I figured if I could get something quicker to show a calculation so I know if I need to also recheck that employee.

I am sure I could enter a bunch of formulas on that worksheet to do that task, but I was curious if it is possible to do this with code?

I was then wondering if this is possible, could I use a conditional formatting to highlight the field red if there is no data entered, but only once that date is current, so that I can see if I missed someone, easily? I dont want to see a sheet that is entirely red, because it is in the future.

Also if this is possible can you help show me the logic in the code, I am just learning about code, and I really want to pick this up myself.

**Staci** · 03-06-2011, 07:20 PM

If this is not possible, is it possible just to put an x in the example, to show that that person was entered into the log?

**jaslake** · 03-17-2011, 07:04 PM

Hi Staci

This'll get you started. My Grandson just arrived to spend the night so I'll not work on your issue 'till at least tomorrow afternoon.

Since you wish to

I really want to pick this up myself

I'll give you an approach.

1. You'll need to add additional Dim statements to the Submit Button of the UserForm.

Dim totHrs As String
Dim myName As String
Dim myDate As Date
Dim myCol As Range
Dim myRow As Range

2. You'll need to accumulate the "totHours". This will need to be done at the end of the section of code that begins with

Please Login or Register  to view this content.

You'll need to use the "Val" function here

Please Login or Register  to view this content.

3. You'll need to add code to the Submit button of the UserForm (This code needs to be added BEFORE the UserForm is cleared). In this code, you'll need to use code like

Please Login or Register  to view this content.

and like

Please Login or Register  to view this content.

4. In the Attendance sheet, you'll need to find the intersection of the Employee Name and the Date from the UserForm and set the value of this intersection to the value of "Total Hours" with code like

Please Login or Register  to view this content.

I've done and tested this part of the coding; if you want it, I'll be glad to share it with you. The colors part hasn't been done...I'm not a fan of colors as I'm color blind...but I'd assume we can make something work for you.
Let me know how your efforts go!

**jaslake** · 03-24-2011, 04:42 PM

Hi Staci

Just curious...making any progress? Do you need the code I have or are you going to work through it?

**Staci** · 03-27-2011, 02:06 PM

I apologize John, I got tied up as there were a lot of "flack" about switching to this method. So I had to take a look at the original method, and figure out improvements.

I do appreciate the time that you took on this, but I was not sure how or if I could delete a post, as thanks to the "powers that be" i will not be able to proceed forward.

If you are familliar with looping through codes, you may want to look at my newest post.