Automated Attendance Sheets

[Guest]

Hi,

I had posted this question a few days ago, and Dave P. replied on Oct 05
2004. let me rephrase the question.

A master sheet contains data for all employees shift details for all days of
the month . The sheet looks something like this

Name 01/10/04 02/10/04 03/10/04 04/10/04 05/10/04 06/10/04 .....
a D N D N
D O
b N N O D
D D
..
..
..

What I'd like Excel to do is this:
1. A menu which asks the user to select the date, and the shift (D/N/O).
2. Looks up the date across the columns
3. Searches for the particular shift.
4. Prints out the name of the employee for that day for the selected shift
and with the Date and Shift selected as the header on all pages.

Hope you can help

Thanks and regards,

Neil

 

[Guest]

Hi Neil,


I've designed something like this in the past, based on what you've written
I'll try to help you out. This will require some VB knowledge. I'll answer
the questions you've written below, you try them and let me know how you get
on and we'll go from there.

Okay here we go...

You want a menu to ask the user to select the date and the shift.

Design a User form and add a calendar control for selecting the dates.
List all possible shifts in a column, highlight it and give it a title.
Set up a list box and use it's title as the control source.

If you've done this, you will now have a list of shifts and the option to
select a date.

In the code section of VBA, declare a string for the shifttype, and a date
for the Calendar date. Also declare a counter as an integer.

add a button. The code for this button should select the upperleft cell of
the attendence sheet and use the OFFSET funtion to move through the cells.
TO read about OFFSET in detail, use the help section. But essentially you
want the write something like this:

do until activecell.value = mydate(or whatevername given to date)
activecell.offset(0,1).select '"This will move through the cells one by one
counter = counter + 1 ' "This will increase the counter
loop

the above code will move through the cells, increasing the counter by one
until it finds the date required.

Now select the upperleft cell again and use the offset function to move down
through the cells until you reach the shift required. You'll need to
reverese the polarity of the Offset function, i.e. from Offset(0,1).select to
Offset(1,0).select

When you reach the cell of the shift, get the code to read what ever is
x(counter) cells away. In otherwords, if it took 18 moves to get to the
required date from the upperleft cell, the NAME will be 18 cells backwards.

NAME = activecell.offset(0,"-"& counter).value

This will read the name and give it back to you.

Now, if you have a second sheet open, simply find the range you want to put
the name into, and declare that cells value equal to NAME.

I hope that made sense, it will require some intrepretation, I'm sure...

Name the range that you want to put the NAME of the employee into, and get
the code to select the range. Then use:

ACTIVERANGE.PRINT

Let me know how you get on.