Auto fill shift in calendar

[Airborne]

Hello, first of all.....happy new year to all :vv

We have a timesheet where we fill in the hours we work during a month. We have to fill in the shift as well. The shifts we work are called Morning, Afternoon and Night. The default working hour is 8. The shift names are One, Two, Three, Four and Five.
We would like to open the timesheet, click on the button LOAD FORM. The form will show up, you pick the shift and the year, click on FILL CALENDAR and all the sheets (January, February, March, etc) will be filled with the shift (Morning, Afternoon, Night) in Column ("D") and the 8 in front of the shift in column ("C").
I've attached a sheet to make clear what I mean. It only has to fill the columns C and D. The other columns are only filled now to make clear how the shifts rotate. I hope it's all clear :blush

Thanks and regards.

[Ken Puls]

Hi there, Airborne!

Sorry I haven't replied sooner, but things have been kind of nutty for me lately.

I don't really have an answer for you at this point, but I do have some questions...

It looks like your company works on an 18 day rotating schedule all together, is that correct?

To sort of paraphrase what you're after (to make sure I'm on the right page) you really want to enter your year and shift, and have it look up all the actual shifts scheduled (in the 18 day rotation) and populate your worksheets throughout... correct?

Your 18 day rotation starts as of 01/01/2005 I believe, but just a confirmation...

If I'm right on all of the above, I really don't think that you actually need VBA to do this. You could actually use formulas to do all of it, with maybe a couple of worksheet bound combo boxes to change the Year and Shift. It would take a bit of work to nail the formula down, but I think it could be done.

Basically, the trick to nailing it (either formula or VBA) would be that you'd need to figure out what day each line represented in your table (1, 2... 16, 18) and then you could use lookups.

Let me know if I'm off pace here though.

[Airborne]

Hi Ken:vv .

You are right, we work on a 18 day rotation (with days off as shown in my sheet).
We would like to start with the January sheet, that's why I only made a button on that sheet.
I've modified the form. I think everything is there to make it work. You choose the start day (because the days of the whole year have to be filled on the sheets), you choose the first rotation and the day of the first rotation (on my January sheet we should choose day 1 and 4M). It then must be possible to follow the rotation (you may have to fill in a few days after the rotation is filled on all sheets. E.g. shift 2 will start 4A on the 6th of January so they have to fill in 2 nights on the 1st and 2nd but that's no problem. There is also the problem of leap years. I don't have a clue how to set the formula's though:blush
I know what I need but I don't know how to get it work:mkay (it's a common problem:D ).
I'll upload the new sheets. Thanks again for your time.

Regards.

[Ken Puls]

Heya Airborne,:)

Okay, let's try the attached file out. FYI, this is a very different approach from what you were using, so I'll highlight the changes here.

-This uses no VBA at this point at all (although I left your userform and module in there), but rather is 100% formula driven
-I have put all months into one sheet, as it makes it easier to follow whats happening (for me anyway)
-I have eliminated all of your info on the utils sheet, but have added a table of the shift schedule there. After looking at it more closely, I figured that you're actually on a 35 day rotation as well.
-When you access the schedule sheet now, click on the "Shift" cell (in green) and a data validation list will pop up with the available shifts
-Change the start date to anything you want and watch it fly!

This will update 366 days of shifts to the appropriate schedules, using a combination of hlookup, vlookup and mathmatical formulas.

The logic behind the main formula was the tricky part, as you have to figure out how many days have passed since 12/31/04, and what multiple of 35 it is. We know that your schedule is correct starting 01/01/05, so presumably, it will continue on forever from there. With the formula I wrote, it basically says that it will look up the value in row 1 on the first day, row 2 on the second, etc... Day 35, it actually looks up value 0, though, and then the table starts over at one again. :eek: Making day 36 look up 1 again was the tricky part, and hence the long first condition in the vlookup. FYI, if this was done VBA, you'd still need that formula.

I do think a little VBA would help here, though. My suggestion would be:
-Code it to ask what shift and what start date
-Use VBA to determine how much of the schedule to print (maybe use the autofilter to reduce the rows

Give it a shot and let me know if you want to go back to your format, or if this will work for you. (I would have started here anyway, as I wanted to ensure it was all correct before splitting it off into separate sheets.)

Cheers,

[Airborne]

:clap: Fantastic Ken! How did you create that combo box on C1:banghead: ?? I know how to add combo boxes and put data in the pulldown menu but where is the data kept in the box in C1? Does it have something to do with tblSchedule and tblShifts in the formula? I can't figure out how that works:doh: .
Looking at the formula makes me aware of the fact that it's not only VBA but Excel too where I need training:( ...

Is it possible to put every month on a seperate sheet? We fill in the hours, overtime, leave every month. Print the sheet and send it to admin.

Thanks and :thumb
Airborne.

p.s. promotion and new smilies...

[Ken Puls]

Hi Airborne,

Tell me about it! So much to learn in this program!:D

Click in cell C1, then go to your Data menu and choose Validation. You can do all kinds of neat stuff in there. I frequently set up my inventory sheets to only allow numbers in the count columns, etc... You could also link the list to an external list, but since there's only five options, it doesn't make sense here.

As for extra sheets, we can do that, but first, try this. Highlight the row which has your headers in it (Date, Shift, etc...). Go to the Data menu again, and choose Filter-->Autofilter. You'll get some dropdown boxes across the top row.

On the Date column, click the dropdown box and choose "Custom". Set up your filter so that it is greater than 02/01/2005 and less than 03/01/2005.

We could code this via an userform to make it a little easier, or maybe even use the calendar control. If you don't like it though, we could still break it into sheets. Let me know what you think.

Cheers,

[Airborne]

Hi Ken, thanks for the lesson. I've used Validation before (got it from the forum:thumb ) but I didn't know I could use it like this.

I still think it's better to put every month on a separate sheet (with the name of the month). I'm sorry but after testing I saw a problem that I should have told you earlier. Sometimes we work overtime or we work less hours. We have to change the 8 hours to for instance 4 or 12. When I do that now the change will stay there in every month. Also we have to put comments behind the shift, for instance overtime, training, etc. This will stay there also:wot
Sorry, I'm beginning to be a pain in.......again.

Cheers.

Airborne.

[Ken Puls]

Hi Airborne,

You're not being a pain. The project is about making it work for you!:)

We can break it up into individual sheets, but what's the main purpose here? We can code something to re-write all the forumlas and clear the comments, pretty easily. Will that, coupled with new sheets, be the right path, or will you need to hold numbers in there for any period of time?

And yeah, the validation is neat stuff! :yes Someone recently sent me a spreadsheet which popped up a comment on every cell you clicked (kind of dynamic) which was all done in data validation. I hadn't seen a use like that before!

Cheers,

[Airborne]

Hello Ken,

When I'm not a pain I would really like seperate sheets called Januari, February, etc:giggle . The main purpose is that we print monthly sheets and send them to admin. Also the comments and altered hours are a problem. I'll send a sheet to show you what I mean. When I change and save the hours and add comments and go to the next month the changed hours and comments are also in that month:wot . The timesheets have to be saved also. It must be possible for us to look back.

Cheers,

Airborne.

[Ken Puls]

Hi Airborne,

Sorry again for the late reply. I've downloaded your file and played with it a bit, and have a new one for you. A few things to note though.

I have still stuck with one sheet for now. If you really want to have multiple sheets, we can do that, but honestly, I think that you'll get better functionality this way. As I see it, the main benefit of having a single sheet is that the file begins to act a bit as a database. Because all of your data is in one place...

-it makes it really easy to code certain things, without having to deal with multiple ranges
-the list can be filtered by code to give the user the impression that they are dealing with multiple sheets
-we can hold information for a whole year, then update the file to take new info

Download the attached, and take a look at the changes I made to the interface.

-You'll notice that you still have the Shift in a validation dropdown. Try changing it, and see what happens. You should see the timesheet for each shift pop up so that the get their own schedules.
-I've also changed it so that you choose the month in C2. This also works on a validation dropdown. Play with that one for a bit, and watch how the calendar will change for you.
-One important thing to note here is that everything is still pre-loaded with formulas, but you will want to allow the user to overwrite them. When you flip back and forth between shifts/months, your data will stay there.

Basically, the way this works is by triggering the Worksheet_Change event. If the cell changed was either of the validation boxes, it will hide/unhide the appropriate columns or rows to leave you with the correct data showing for your calendar. I'm thinking that this should satisfy your head office, as you can store your data all month (or year for that matter), and then send them a sheet for each shift.

Now, what about the formulas next year? I would suggest that we code a routine to clear all the data entered in the users fields, and re-establish all the formulas. (It's harder than it sounds.) You'd trigger this macro once per year, which would basically ask what year, update all the dates, clear the info and re-write all the default formulas. (Oh, and by the way, it is Feb 29 compliant, too!)

As for printing, I think that this approach may be more versatile that splitting it to separate sheets. With the setup I gave you, you're not limited to printing only January, February, etc... You could code a userform to ask for a start and end date. It would then filter the list for you. Just think, you've got someone who needs their schedule from May5-Jul18 (don't know why but they do). You drop in the dates, and it filters the list and prints it out.

As I say, if you really want to go to multiple sheets, we can, but it will make the coding a little more difficult, and a little harder to maintain. The coding that I've suggested above in this model will be really easy... trust me!

Honestly, though, I'm game for whatever you want. No hard feelings here at all. I don't want to force you to what I think is best, but I really believe that this will give you more options for the inevitable growth that always happens to these systems. :giggle

Let me know what you think,

[Airborne]

Hi Ken, sorry for this late reply but again..so little time:( . I'm going to give it a try. By it I mean everything on one sheet.

My problem is now that, because I didn't provide you with all the data on our monthly sheets, I have to try to add that to just one sheet (I didn't put all the data in the uploaded sheet because I just thought that it wasn't important:banghead: ).

I'm going to try it myself first. I'll come back to you if I fail (which will happen :*) ) or when I get it to work.

Thanks and cheers.


p.s. As my father always told me, you should study more. In lesson 10 you learn about using validations....so :whip

[Ken Puls]

Hey Airborne!

Just wanted to follow up and see how you were making out with this...

:beerchug:

[Airborne]

Hi Ken:( .

As I said before, I didn't put all the data in the uploaded sheet because I didn't think it was important. But because everything will go in one sheet the data I didn't put in will not be easy to add. I'll upload the timesheet. I've added the missing data to the Utils sheet. You will see that the extra data has more cells then the total month. I've added some comments at the bottom where some values have to go to the next month.
I hope it's all clear to you what I mean:doh: .

I'm not sure what I can do with the month option on the top of your timesheet:think:

I hope you can still find the energy to work on this thread because it's beginning to get a bit confusing:mkay


Thanks anyway Ken and cheers,

Airborne.

[Ken Puls]

Heya Airborne!

Nice avatar! Now you just need to upload a pic and we'll know everything about you! :rotlaugh:

Okay, so I concede on the multiple sheets. I think you're right, it may not really be feasible to make it work in one list. The columns aren't at issue, but the subtotals with calcs may be.

I'll tell you what... I still have the energy, but have some other stuff to work on tonight. I'll take a look tomorrow night, and already have some idea of how to make it work for you, but another night to mull it over might help.

I'll get back to you soon, :yes

[Ken Puls]

Hi Airborne,

Can you do me a favour, and build the January to December sheets, exactly as you'd want them, with maybe some sample data in January & February? I'm thinking that I have some idea on where to go, but would rather see that first.

Also, as this looks more like a timesheet than a calendar now, are you going to have a separate workbook for each person?

Thanks,

[Airborne]

Hi Ken,

I'll upload a new sheet. It looks the same as the one we use now. On the January sheet I've added all the shifts. It is important that we sometimes add some comments in the cells where the shift is in (e.g. Morning & overtime or we change Morning to Afternoon because of a temporary shift change).



Also, as this looks more like a timesheet than a calendar now, are you going to have a separate workbook for each person?
Yes it is a timesheet and we have a seperate sheet for each person. I guess I used the wrong title....it should have been timesheet and not calendar which by the way is misspelled in the thread (I'll have to subscribe to a forum where I can learn proper English :rotlaugh: ).


Thanks again for your patience and cheers,

Airborne.

[Ken Puls]

Hi Airborne,

Okay, I've got something for you to play with. I've managed to get it set up pretty well completely (I had fun with this).

So here's what we've got...

1) I added back a Utils sheet, which holds all of the following:
-3 named ranges called "StartDate", "Shift" and "EmployeeNumber"
-the shift table matrix

2) I then set up each sheet with the formulas to look up the correct shift based on the day.

3) I have set up the formulas that carry over to the next sheet, but I have not set up the following formulas on any of the sheets: Monthly Total, Desired Payout, Time for Time or Total Payout.

4) I have created a userform and some macros for you

Initial Employee Setup
Just click the button on the Utils sheet, and it will launch the userform which asks for the Employee Number, Year and the shift. It validates that information has been entered, then plugs that info into the Utils named ranges. It then calls the "ReEstablish" macro.

Annual RollForward
Just click the button on the Utils sheet, and it will copy the ending hours balance from December to the opening hours balance on January and increase the date in the Utils sheet by one year. It then calls the "ReEstablish" macro.

The "ReEstablish" Macro
This baby does pretty much everything... It loops through each Monthly sheet and:
-plunks the first day of the month in Cell A8
-rewrites all the formulas in columns A to D (Just in case your users overwrote any)
-clears out any hours that may have been entered in the data entry fields, as they aren't needed for a new user or when the year is rolled forward. (Remember that the annual process does copy the closing to the opening, and those stay)
-clears any days listed on the sheet that are past the end of the month (bye, bye March 1-3 when only 28 days are in February!)
-protects the sheets (all green cells are uprotected) so that the users can only overwrite things in green.

Just be aware that much of this is coded with actual rows in the code, so if you insert rows on any sheets, we may have to adjust it. Fortunately, your monthly sheets are virtually identical, which made it really easy to loop through them, so we should only have to update in a couple of places at most.

I think that's about it... and it's :sleeping: for me now!

Let me know if you have any questions.

[Airborne]

The saying.....All good things come to those who wait is true.

:bow: This is really great Ken. This is what I wanted:thumb .

To make it perfect....is it possible to make it easier to enter data in the shift column (morning, afternoon...)? Sometimes we work a different shift for a few days. Also we add some comments in that cell (e.g. overtime or shift change). When I now doubleclick on a cell in the column I get the whole formula presented. I can just select the cell and type comments in it but that may not be clear to everyone who will use the timesheet.

The rest is perfect, thanks.

Cheers,

Airborne.

[Ken Puls]

Hi,

Well, we could just add a new column for comments if you like?

My thoughts with a change in shift was that the user should probably just overwrite the formula with the shift, as it will be random.

The beauty is that even if they overwrite it, it will rewrite all the formulas when they roll it forward to a new year.

I was thinking also that it might be nice to put in a menu instead of having the Utils sheet showing all the time. Easy to do, if you like.

Let me know.

[Ken Puls]

Heya Airborne,

Okay, done some more fooling around, but am not going to post up tonight as I want to make sure on the other questions.

I have hidden the formulas, so if your user double clicks on the cell, they'll never know that there was a formula there. Unfortunately, this creates an issue...

If the user decides that they made a mistake and presses ESC, no big deal. If they do anything else, though, the formula is destroyed. I have coded a routine to regenerate the formulas, but that will also have to be assigned to something. I'm thinking that a nice simple menu should do the trick, but just want to get the okay from you first on that.

Question still stands on the comments, too. Seperate column, or does the hiding of the formulas take care of that issue?

Cheers,

[Airborne]

Hello Ken,


Question still stands on the comments, too. Seperate column, or does the hiding of the formulas take care of that issue?

I think that will take care of the issue.



I'm thinking that a nice simple menu should do the trick, but just want to get the okay from you first on that.

I think that would be great:yes . I agree about hiding the Utils sheet.


The beauty is that even if they overwrite it, it will rewrite all the formulas when they roll it forward to a new year
This sure is great. I've showed it to one of the guys at work and he was really impressed although I couldn't explain to him how all the formula's work (I'm still trying to understand):rotlaugh:


Cheers,

Airborne.

[Ken Puls]

Heya, Airborne,

Okey Dokey... here's a version to fool around with, and naturally, some more notes! :rotlaugh:

-I've added another column to each sheets for "Notes"
-I've added "Name" to the setup prompts as well, instead of just employee number
-I've created a menu for you called "Timesheet" (although this can be changed), which will be created upon opening the workbook or activating the workbook. It will be deleted before closing the workbook and upon deactivating the workbook as well. (Keeps it from showing up when your users switch to a different Excel spreadsheet without closing.)
-I've hidden the formulas in columns C and D, and set up a procedure to protect all the worksheets when the workbook is opened. This leaves the data in the cells, but if your user clicks in the cell then leaves without pressing escape, the formula is killed.
-Because of the above, I set up a menu item to Restore default formulas in the rows. Simply highlight ANY cells in a row that got nuked, run the proc, and the default shifts and hours will be put back in.
-Since the worksheets need to be protected to hide the formulas, I wrote a couple more subroutines as well (in the Timesheets|Admin Tools) menu. Protect all sheets, and Unprotect all sheets. You will need to enter an admin password to unprotect them, but we'll get to that.
-I have hidden the Utils sheet, which now also holds the settings for you menu and the defaults that the formulas are based on. I therefore wrote you two procs to Hide/Unhide the Utils sheet, which also hide in the Timesheets|Admin Tools menu. You'll need that admin password to run the Unhide macro there as well.

-The main two procedures are "Set up New Timesheet" and "Roll Forward to New Year" The first collects the user info then rebuilds the timesheet file formulas, and then activates the current month's timesheet. The second copies the December ending (carryforwards) to the January opening balance, then rebuilds the rest of the timesheet after adding 1 year to the date.
-Obviously, you'll want to start with the "Set up new Timesheet" first, but try entering some data and rolling it forward to a new year as well to make sure it works correctly.

You will still need to review you formulas for rows 39-45, although I tried to guess what they were. You will need to unprotect all the sheets to modify them, though!

Open the file up in the VBE, and take a look at the code.
-The menu maker code (based on John Walkenbach's) is all contained in the ModMenuSetup module.
-The code for the actual procedures fired are all in the modProcedures module, and have been separated into those called from the menu, and those which are "utility code". (Re-usuable chunks called from multiple procedures.
-There is also code in the userform, which calls a proc from teh modProcedures module
-The ThisWorkbook module has a few events programmed as well, which calls several procs from the other modules.
I'm sure it will make sense when you look at it. I tried to document the code fairly well, and always use the Call keyword when I am calling another macro. :yes

Last thing to note is that at the beginning of modProcedures, there is a constant called AdminPass. This is the admin password for the code. There are two purposes for this constant:
1) To use the Unprotect All Sheets or the Unhide Utils Sheet macros, you must supply this password. This is to keep your users from playing with it without your knowledge. ;)
2) It is the password used to protect all the worksheets.

Keep in mind, that this will not keep out any user who knows Excel, as I did not protect the VBE, so they can get in and look at the password. You can change it to whatever you want, but keep the following in mind.

The password IS case sensitive if you want to unprotect a single sheet. If you run any of the menu items I wrote that ask you for it, however, I have coded it so that it IS NOT case sensitive. One weakeness in what I wrote, however, is that the password is not masked when you enter it. Unfotunately, that's not easy. It's not really meant as true security anyway, but more as a hinderance to make people think about what they're doing. The password that I set up for you is "usecaution"

At any rate... play with it a bit and see what you think. If you want any explanations on anything, just let me know! :yes

[Airborne]

Hi Ken. One word.....FANTASTIC!:clap:. The timesheet menu is great.


But...:banghead: , I'm afraid I messed things up a bit. After I tried your sheet I thought let's adjust the formula's for rows 39-45. I unprotected all sheets and put in the formula's. I also added some text in E39,E40. I merged cell F45,F46 because numbers with 5 digits don't fit in the cell.
When I now start setup a new timesheet I get error 1004....unable to set locked property of the range class.

In Sub ReEstablishSheets() clear payout cells I've changed .range("E40") to .range("F40") because it would delete the added text.

I'm trying to figure out now where the error comes from. I'll upload the adjusted sheet.


Okey Dokey... here's a version to fool around with I surely fooled around with your new version:rotlaugh: .


Thanks for al the work and cheers,

Airborne.

[Ken Puls]

You did WHAT???? :eek: Jeez, man... I told you to play with it, not break it!

:rotlaugh:

Okay, seriously, here's the thing...

You were quite right to change this part of the code:

'Clear payout cells
.Range("E40:H41").ClearContents
.Range("K40:M40").ClearContents

I missed updating that when I inserted the new column. Thought I caught them all... Should have been:


'Clear payout cells
.Range("F40:I41").ClearContents
.Range("L40:N40").ClearContents

As for the error... well... let's just say that merged cells were the devil's gift to Excel. :devil: They look pretty good at first, but they're poison in virtually any spreadsheet. My advice... never use them if you can avoid it, and even then only as a last resort. Invariably, they just bomb VBA code. Instead, you have a couple of choices.

My first preference would be to just change the width of the columns so that they'll display five characters. If that makes your spreadsheet look funny though, you could try the other.

The practical alternative to merged cells, which I use all the time, is "Center across selection". In this case (once you've removed the merge), highlight F45,F46 (you sure you don't mean F45:G45?). Right click and choose "Format cells|Alignment", and from the Horizontal combo box, choose "Center across selection". See how that works for you.

FYI, I just noticed that you have data in rows 47 on. You may be able to get rid of some of those now as well, or just code them by formula as well.:yes

Cheers,

[Airborne]

Hi Ken. I'm recovered from the shame of messing up your sheet :rotlaugh: .

What rests is.......:bow: :clap: :thumb etc.


let's just say that merged cells were the devil's gift to Excel
I'll remember that.


The practical alternative to merged cells, which I use all the time, is "Center across selection".
I've used the alternative and great, it works. The error message is gone.


FYI, I just noticed that you have data in rows 47 on. You may be able to get rid of some of those now as well
The only thing I want in those rows is that I want the Name and Employee number put in the cells when I set up a new timesheet by using the form.


I'm going to play around with the timesheet this week. When I can't mess it up anymore I'll call the thread solved.


Thanks very much for your time and patience :friends: ,

Regards,

Airborne.

[Airborne]

Hi Ken,

I've been testing the timesheet for some days now and it works great . The guys here are all impressed. So :clap: .

I have some final questions before I mark the thread solved....:blush

1. On the Utils sheet you have created FaceId with numbers (optional). What is it for and what can I do with it?

2. There are also two charts. What can I do with the charts?

3. I want to protect the showing of the Utils sheet with a different password then I use for unprotecting the sheets. I've tried this Public Const AdminPass = "usecaution" and added Public Const AdminPass2 = "usecaution2" then in Sub ShowUtilsSheet .Unprotect Password:=AdminPass2 This will unhide the Utils sheet but it will then give me a error.:dunno.

The reason that I want to use two passwords is that we all have to use the unprotect all sheets because in January we have to add the new days to our leave but I don't want everybody messing with the Utils sheet.

4. On the UfRollForward form I want a combobox for the shift and not a listbox. I've tried to do that. Named the Combobox lbShift but I got a error in If lbShift.Selected(i) Then
.Range("Shift") = lbShift.List(i)

5. Is it also possible to make a combobox for the employee in the form and if
you pick the employee the number is filled in in the number textbox?


I can imagine you doing this :motz2: when you read all this. I understand you're very busy. I'm trying to solve the above questions:mkay and I'm patient:rotlaugh:

Cheers,

Airborne.

[Ken Puls]

I can imagine you doing this :motz2: when you read all this. I understand you're very busy. I'm trying to solve the above questions:mkay and I'm patient:rotlaugh:

:rotlaugh:Honestly, it takes a lot to make me :motz2:! I think this is great! I enjoy the fact that you're interested in changing things, as I always learn things along the way. :yes It's also really cool that you're learning how this all works as well!

Now, check this (http://www.vbaexpress.com/forum/showthread.php?t=1854)out... Malcolm's post (#4) should get away from our irritating problem with the passwords for the sheet protection showing up when we put in a password, but we may need to look into Jake's KB entry (http://www.vbaexpress.com/kb/getarticle.php?kb_id=33) to deal with the multiple passwords, but I'll try to look at that in a little more detail later tonight before I can confirm that for sure.

It might be a good idea for you to upload the latest version of the workbook? (Strip out any sensitive data if you have to.) I just want to make sure we work with any changes you've already made.

[Airborne]

I'll upload the sheet. The only sensitive data are some Dutch words (is that also one of your skills?:rotlaugh: ). I don't think the changed words are important though. I have to change the rest to Dutch later.


One request from the guys here I forgot to mention is, that they want to save the timesheet at the end of the year. I think that will make the choice "go to next year" unnecessary. I can leave it in but the mistake (even with a password) of deleting the data of a whole year is possible.

Thanks.

[Ken Puls]

I'll upload the sheet. The only sensitive data are some Dutch words (is that also one of your skills?:rotlaugh: ). I don't think the changed words are important though. I have to change the rest to Dutch later.

My wife used to date a Dutch guy... so I can get them translated! :rotlaugh:



One request from the guys here I forgot to mention is, that they want to save the timesheet at the end of the year. I think that will make the choice "go to next year" unnecessary. I can leave it in but the mistake (even with a password) of deleting the data of a whole year is possible.

I think you'll find that the RollForward is still necessary, as it rebuilds the calendar, and sets it the opening date to be correct. I would suggest they save it under a file name, run the procedure, then save it again (under a new file name). We can atuomate that too, if you like.

I'll take a look at the rest when I get a bit of free time over the next few nights. :yes

[Airborne]

My wife used to date a Dutch guy How could she swap a Dutch guy for a Canadian guy? (is that proper English?):rotlaugh: .


I think you'll find that the RollForward is still necessary Yes as usual you are right. I did save it under a filename etc. That works fine. We save it in the format Name_Timesheet_Year. And yes automate it:yes . I'm thinking about when they use the RollForward in January it changes the filename. It can use the year in the timesheet.


Thanks Ken,


Airborne.

[Ken Puls]

Heya Airborne!

Just an update for you, and an answer to some of the unanswered questions:

1. On the Utils sheet you have created FaceId with numbers (optional). What is it for and what can I do with it?
-The FaceID is what drives the picture on the menu items for the custom menu. Jonhn Walkenbach has a free add-in (http://j-walk.com/ss/excel/tips/tip67.htm) to help identify what picture is what. Try downloading it and taking a look. FaceID's are totally user preference, so whatever you want to use is cool. Just remember that you need to recreate the menu to get them to work. You can just run the CreateMenu (I think that's what its called) to make it happen.

2. There are also two charts. What can I do with the charts?
-Hmmm... Don't remember any charts. I'll take a look in your file and see

3. I want to protect the showing of the Utils sheet with a different password then I use for unprotecting the sheets.
-I'll try and look into this tonight


4. On the UfRollForward form I want a combobox for the shift and not a listbox.
-Okay, I'll give that a go tonight as well.

5. We save it in the format Name_Timesheet_Year. And yes automate it:yes .
-Not a problem. I can make that change tonight as well.
A question, though... do you have an idea of how to do this? It would go something like this: ThisWorkbook.SaveAs(but what goes in here?)

I'll also try to build a userform to show ***'s when someone puts in the password, instead of the actual word, as referenced in a thread above.

Was there anything else that you can think of at the moment? (You can still add more afterwards if you want! ;))

[Airborne]

Hi Ken,


1. On the Utils sheet you have created FaceId with numbers (optional). What is it for and what can I do with it?
-The FaceID is what drives the picture on the menu items for the custom menu. Fantastic, I understand now. I've added a extra item to the timesheet menu with icon and assigned a macro to it just for fun. It works.


A question, though... do you have an idea of how to do this? It would go something like this: ThisWorkbook.SaveAs(but what goes in here?)
The first line is the easy part:*) .
I've tried


Sub SaveNew()
Dim sName As String
Dim sYear As String
sYear = Worksheets("Utils").Range("O2")
sName = Worksheets("Utils").Range("N5")
ChDir "C:\Temp"
ActiveWorkbook.SaveAs Filename:= _
"C:\Temp\" & sName & sYear & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

I want to save the new file to my c:\Temp for testing. on the Utils sheet in cell O2 is the year and in N2 the name. But I'm doing something real dumb because it saves the file in c:\Temp as & sName & sYear & ".xls"

I'm working on it.

Thanks again :friends:

Airborne.

[Ken Puls]

Hi!

Okay, in the file posted above, the date is actually in N2, not O2. ;) Doesn't seem to be anything in 02 at all. I changed your code to read from O2, but that gives us an issue.

Since sYear is a string, it picks up the date as 01/01/2005. No biggie, except that the / is not a valid character (or it is interpretted as a directory switch). We can fix that using the Year function as I've shown in the code below.

I kicked out the ChDir line, as it actually isn't necessary. That will change the directory for all future open/save commands until Excel is closed. My guess is that you don't want to point there in future, but rather just stick the file in a backup file and leave it there.

I've also changed to use the SaveCopyAs method (sorry, led you a bit astray!) since this will save a backup copy under the desired name, and avoid having to resave it under the Timesheet name.

Lastly, it included it in a With clause... actually for two reasons. One because it made it faster, the second is because you protected the VBA project, and I can't open it up! :rotlaugh: Can you PM me the password so that I can work on it some more?:yes

Here's the modified code:


Sub SaveNew()
Dim sName As String
Dim sYear As String
With Workbooks("Timesheet.xls")
sYear = Year(.Worksheets("Utils").Range("N2"))
sName = .Worksheets("Utils").Range("N5")
.SaveCopyAs Filename:="C:\Temp\" & sName & sYear & ".xls"
End With
End Sub

If you put it in to your file, maybe upload a new copy as well. If not, I'll just add it in when I work on it tonight.

:hi:

[Airborne]

Yes Ken
the second is because you protected the VBA project that's really smart hiding the project for the creator/teacher:( . I'll upload a new one. I'm afraid there's more Dutch in it:rotlaugh:

Do you remember
Was there anything else that you can think of at the moment? (You can still add more afterwards if you want! :giggle . In VBA lesson 6 is a great function for Easter date. Now it would be nice to get that in. When working on Easter people get extra %. I don't know how and where to put it in, I'm thinking about it.

I'll test the save code now.

Thanks,

Airborne.

[Ken Puls]

Hi Airborne,

Wow! You really Dutched the menus! I'm glad the programming is still in English, or I'd be in trouble! :rotlaugh:

I've got to call it quits for tonight, but will try and get back to this tomorrow evening. Here's what I've got accomplished so far:

1. There are also two charts. What can I do with the charts?
-Nothing, they were garbage. I deleted them

2. I want to protect the showing of the Utils sheet with a different password then I use for unprotecting the sheets.
-Okay, done. The Admin password is still "usecaution" and will protect/unprotect all sheets except utils. The Super password is "superpass" and will protect/unprotect all sheet inlcuding utils. The actual passwords are "usecaution" for all sheets except Utils, which is "superpass" if you need to do them individually

3. On the UfRollForward form I want a combobox for the shift and not a listbox.
-Sorry, didn't get there yet.

4. We save it in the format Name_Timesheet_Year. And yes automate it:yes .
-Done. I added a field on the utils sheet called "BackupDir". I loaded it with C:\Temp to start, but change it to whatever you want. (Don't add a slash on the end as the code will do that for you. I also consolidated the procedure we perfected above into a one liner (with line continuations), and coded the references to the named cells. This means that you could move the table on the utils sheet without having to recode it. (Use named ranges whenever you can, as VBA does not update real references.)

5. I'll also try to build a userform to show ***'s when someone puts in the password, instead of the actual word, as referenced in a thread above.
-Never quite got there, either.

6. Easter... I would ask :giggle. I think I'd work out Easter in the VBA code, then search the correct sheet for the date using .find. Once I had that, I'd replace the formula you need to. Just remember that we'll also need to reset that formula for next year. :yes

I'll try and work on the rest a bit tomorrow night, but you do what you can in the mean time. If you could test out the protection stuff fully, too, that would be good.

:beerchug:

[Anne Troy]

Ken: It's really admirable the way you stick with people in their threads here. :)
Airborne: You're soooo lucky! (I see that you know that, tho!)

[Airborne]

Airborne: You're soooo lucky! Yes Dreamboat, I sure am lucky. I'm thinking of how to do something in return. I've already put in some Dutch words into the project. I can offer Ken free Dutch lessons:rotlaugh: .

[Airborne]

Hi Ken,


I want to protect the showing of the Utils sheet with a different password then I use for unprotecting the sheets :yes great Ken, thanks.


4. We save it in the format Name_Timesheet_Year. And yes automate it:yes .
-Done. I added a field on the utils sheet called "BackupDir". I loaded it with C:\Temp to start, but change it to whatever you want. (Don't add a slash on the end as the code will do that for you. I also consolidated the procedure we perfected above into a one liner (with line continuations), and coded the references to the named cells. This means that you could move the table on the utils sheet without having to recode it. (Use named ranges whenever you can, as VBA does not update real references.) What can I say....great it works and it's easy for me to change the directory.:bow:


If you could test out the protection stuff fully, too, that would be good.
Tested! :thumb .

[Ken Puls]

Hey Airborne!

I've got an idea... let's get you to work on the EasterDate part. :yes

Try building the macro to identify the Easter date for the year based on the the date on the utils sheet. From thre make it go to the right worksheet and highlight the date. We'll want to use that as our base for changing your formulas.

If you assign EasterDate to a variable (as Date type), you should be able to acitvate the right sheet using worksheets(format(EasterDate,"mmm")).activate You might need four m's though.

After you can activate the sheet, then try using the .find method in the date range.

Let me know what you come up with.

[Airborne]

Great Ken.....homework:whip :rotlaugh: . I'll start on it right away. With your hint I think I have an idea on how to do it. Please give me some time though:*)



Cheers.

[Ken Puls]

:rofl

No problem! I'll be here when you've got questions! :thumb

[Airborne]

Hi Ken. It's going to take more time:mkay .
Try building the macro to identify the Easter date for the year based on the the date on the utils sheet I have this (courtesy of DRJ).


Option Explicit

Function Easter(xYear As Long)
Dim x As Long
x = (((255 - 11 * (xYear Mod 19)) - 21) Mod 30) + 21
Easter = DateSerial(xYear, 3, 1) + x + (x > 48) + 6 - _
((xYear + xYear \ 4 + x + (x > 48) + 1) Mod 7)
End Function

On the Utils sheet the date is in cell N2. The format is dd/mm/yyyy. For the formula to show the date I just need the year. In the cell where I want the date displayed I have =Easter(N2) and I have the format in the cell as custom yyyy. But that doesn't work. I get an error.


:motz2: yes Ken I understand.

[Ken Puls]

Try this out:


=Easter(Year(N2))

:*)

[Airborne]

Hi Ken:( , I guess it's still way out of my league. I managed to have the month sheet activated but that's it. It's in module Extra. On the Utils sheet I have all the days worked out. Easter, Whitsun and Ascension Day.

I understand you're very busy so there's no hurry. We are already very happy the way the timesheet can be used now:thumb .


In the meantime I will keep trying.

Cheers,

Airborne.

p.s. I'll upload the latest timesheet.

[Ken Puls]

Hi there,

Okay, I've modified your procedure so that it will select the Easter date on the correct sheet.


Sub GetEaster()
'Macro purpose: To generate the Easter Date, and select the cell
'containing Easter on the appropriate sheet
Dim Easterdate As Long, Cell As Range
'Determine the Easter Date and assign it to a variable
Easterdate = Easter(Year(Worksheets("Utils").Range("N2")))
'Activate the sheet and select the cell
With Worksheets(Format(Easterdate, "mmmm"))
.Activate
For Each Cell In .Range("A8:A38")
If Cell.Value = Easterdate Then
Cell.Select
End If
Next Cell
End With
End Sub

It still needs DRJ's Easter formula above to work, but it doesn't require the worksheet formula that you built. I just linked it to the cell with the first day of the year (N2), as the function does the rest. I also skipped using .find in favour of a loop, just because I was having issue with the syntax of it. It will run a little slower, but we're only searching 30 cells once a year, so not too big a deal.

I guess that the next step is to make it change whatever formulas you need now. (Don't forget that whatever you change, we'll need to change back later though!) Look up the offset method to see how you can modify the cell.select line to change what you need to.

You will notice that I changed Easterdate to the Long data type. It would still work as a date, but Longs are faster as they only take half the memory.

For reference, your EasterDate2 on the worksheet is also unnecessary, as you can modify the above to just be
Easterdate = Easter(Year(Worksheets("Utils").Range("N2"))) +1

Forgive my ignorance on the Whitsun, Whitsun 2nd and Acension Day, but are they always x number of days after Easter? If so, you can just program those too using the logic above.:yes

[Ken Puls]

Okay, and ComboBox now...

What you're going to need to do is delete the listbox from your ufRollForward, and add a new combobox. Right click the combobox, and select properties. Change the Name to cbShift.

Replace all the code in the ufRollForward with this:

Option Explicit

Private Sub cmdSetup_Click()
'Macro purpose: To set up the calendar for a new user
Dim Problem As Boolean, i As Integer
Application.ScreenUpdating = False
'Validate data
With Me.tbYear
If Not IsNumeric(.Value) Then Problem = True
If .Value = "" Then Problem = True
If Len(.Value) <> 4 Then Problem = True
End With
If Me.cbShift.BoundValue = vbNullString Then Problem = True
If Me.tbEmpID.Value = vbNullString Then Problem = True
If Me.tbEmpName.Value = vbNullString Then Problem = True
'Inform the user if there was an issue
If Problem = True Then
MsgBox "Er was een probleem met je selectie!" & vbNewLine & _
"Kies een jaar met 4 cijfers, kies een shift" & vbNewLine & _
"uit de lijst, en type een naam en nummer!", _
vbOKOnly + vbCritical, "Error!"
Exit Sub
End If
'Put values in cells
With Sheets("Utils")
.Range("Startdate") = DateSerial(tbYear.Value, 1, 1)
.Range("EmployeeNumber") = tbEmpID.Value
.Range("EmployeeName") = tbEmpName.Value
.Range("Shift") = cbShift.BoundValue
End With
'Reset all formulas and shifts to appropriate values
Call ReEstablishSheets
Application.ScreenUpdating = True
Call ActivateCurrentMonth
Unload Me
End Sub

Private Sub UserForm_Initialize()
'Macro purpose: Load the listbox with default values
'Load the listbox
With Me.cbShift
.RowSource = ""
.AddItem "A"
.AddItem "B"
.AddItem "C"
.AddItem "D"
.AddItem "E"
End With
'Enter current year as default
Me.tbYear = Year(Now())
End Sub

You should be all set! If you run through it, you'll see that the code to get the current value from the combo box is actually very easy compared to the listbox. I've never actually used a combobox before, so was a little surprised by that myself! :rotlaugh:

[Airborne]

:bow: Fantastic Ken!


Look up the offset method to see how you can modify the cell.select line to change what you need to.
Ok?, mission accomplished, I think. I'll upload the new timesheet.


You will notice that I changed Easterdate to the Long data type. It would still work as a date, but Longs are faster as they only take half the memory.
I'll remember this:yes


Okay, and ComboBox now...

What you're going to need to do is delete the listbox from your ufRollForward, and add a new combobox. Right click the combobox, and select properties. Change the Name to cbShift.
I tried to add a combobox and change only the Name before but it didn't work. I see now that you made more changes and it works. How can you all put this together in such short time????? I wonder if I will ever be able to create such projects myself:mkay . Small steps I guess but.......

Anyway, I've learned a great deal again from this thread:friends: . I think everything is there. If you can find the time please check the timesheet and if it's ok we can mark the thread solved.

Thanks for the lessons and your patience.

Cheers:beerchug: ,

Airborne.

[Ken Puls]

Hey Airborne!


How can you all put this together in such short time????? I wonder if I will ever be able to create such projects myself:mkay .

It gets much easier the longer you play with it. You'll get there, don't worry at all. You actually got something to start that I never had... a formal training course. :giggle I muddled my way though this stuff on my own for about a year before I finally even found my first forum!

I'll give you two pieces of advice with VBA...
1) Learn to use the locals window in the VBE. When you step through code, it exposes the entire available object model for each variable that you have, inlcuding the details of what that variable actually holds. As I said before, I've never used a combobox till I coded the one for you. I set up a new one, loaded it with values (same as listbox), and knew the value that I chose,. I was then able to quickly find it in the list. Bingo! I knew what the object was that held the variable I was looking for. :thumb (It's a kind of reverse sleuthing, you know? Doesn't always work, but it is a really handy trick.)
2) Give back to the forum. :*) Nothing will make you learn faster than trying to help someone out. Don't feel bad if you don't know the whole answer, or if it seems to take you as much experimentation as it takes the person you're trying to help. They're here because they haven't been able to figure it out on their own, and it's how we all learn. You'll also find that if something can be done better or more efficiently than the answer you provided, chances are someone will drop in and let you know. It's the best, and cheapest training you'll ever find! :yes

Like you, Airborne, I have actually learned a great deal from this post, as I learn from all of the ones I help with.

At any rate, back to the VBA at hand! :rotlaugh:

I checked out the code, and it all looks solid. (I did not retest any of it though.) The only change I might consider (and it is pretty minor) is the following.

Instead of having five GetHolidayDate routines, wrapping them with the GetDutchHolidays and calling that from the RollToNewYear routine, you could pare it down to one GetEaster routine with some additional arguements:


Sub GetEaster(Optional ExtraDays As Long, Optional DayName As String)
'Macro purpose: To generate the Easter Date, and select the cell
'containing Easter on the appropriate sheet
Dim Easterdate As Long, Cell As Range
If DayName = vbNullString Then DayName = "Easter"
'Determine the Easter Date and assign it to a variable
Easterdate = Easter(Year(Worksheets("Utils").Range("N2"))) + ExtraDays
'Activate the sheet and select the cell
With Worksheets(Format(Easterdate, "mmmm"))
.Activate
For Each Cell In .Range("A8:A38")
If Cell.Value = Easterdate Then
Cell.Select
ActiveCell.Offset(rowOffset:=0, columnOffset:=4).Activate
ActiveCell.Value = DayName
End If
Next Cell
End With
End Sub

You'd then call it five times from the RollToNewYear subroutine as:


call GetEaster()
call GetEaster(1,"2de Paasdag")
call GetEaster(49,"1ste Pinksterdag")
call GetEaster(50,"2de Pinksterdag")
call GetEaster(39,"Hemelvaartsdag")


The advantage of this is that you only have to maintain one subroutine. If something changes on you, you only have to update it in one place. It's probably not such a big deal here, but it is something to keep in mind going forward for your other projects.

A couple of other things I did notice too:
-your May sheet is missing formulas in cells D47, D49 & D51.
-you've hidden a large amount of the formulas, but D2 & H1 aren't hidden on any of the sheets.

Let me know if you have any questions!
:hi:

[Airborne]

Hi Ken,


I'll give you two pieces of advice with VBA... :yes Thanks for the advice. I've been following all threads and although I'd like to help, most of the time I only learn from answers given by others.



The only change I might consider (and it is pretty minor) is the following.
Yes I've changed it. It sure is shorter and like you said "If something changes on you, you only have to update it in one place."



A couple of other things I did notice too:
-your May sheet is missing formulas in cells D47, D49 & D51.
-you've hidden a large amount of the formulas, but D2 & H1 aren't hidden on any of the sheets Yes I've gone through all the sheets and everything looks perfect now.


So, thanks again for the lessons and your patience, this thread is solved! :beerchug:

Cheers,

Airborne.