Automatic week commence date needed

Attached is a sheet I use for my pubs to send me their financial information.

Unfortunately, like most staff - they become lazy and need to be kept in line, but when it comes to money, I need it all to work out exactly.

The sheet seems to almost perfect for what I need now, but for the week commencing date on the bar business sheet.

This is the master date for all the figures inputed so I need the week commencing date to always be correct from a Monday.

At the moment the formula I have in ensures that, but when I open it the following Monday to get the figures the date changes to the following Monday from when the sheet was started.

So...is there a Makro that can run that when you open the sheet for the first time it will ask for the week commencing date which will then lock that date or would it be better inserting another sheet called calendar with week numbers and do a lookup function so that a specific week number relates to a specific week commencing?

Can you help me with the actual formula/VBA coding if either is possible?

Thanks

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

I'm presuming your title change is acceptable since it now depicts your need accurately.

I am also presuming the sheet you've uploaded is a "template" that must be initialized each week, thus your issue with when it is opened. Adding in a macro will require your users to enable macros on their system or at least when they open the sheet.

So, if this IS a template, first...UNLOCK cell B1 so that it is editable, clear the cell so that it is empty....then lock your sheet.

On the Menu Bar just to the left of FILE : EDIT : VIEW : INSERT you will see an Excel icon. Right-click on it and select VIEW CODE. This will open the WorkBook Module for you.

Now, paste in this code:

Please Login or Register  to view this content.

This macro will see the empty cell, add the commencement date, convert it to a real value, and lock the cell so it is no longer changeable.

The name is JB....
How to use the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Locate ThisWorkbook in the Project Window on the left...if it's not open press Ctrl-R to open it
4. Copy and paste in the code (given above)
5. Get out of VBA (Press Alt+Q)
6. Follow the other instructions given previously
7. Save your sheet as an Excel TEMPLATE

By saving as a template, when you first open the sheet it will be a fresh copy of the template and it will require you to save it with a unique name. The code I gave you will see the empty cell B1, put in the date based on the formula you asked for, then make the data permanent.

.

So I have done everything you said, and it doesn't seem to work?

Hehe...let's see...

Originally Posted by JBeaucaire

...if this IS a template, first...UNLOCK cell B1 so that it is editable

,
Cell B1 is still locked...you didn't unlock it.

Originally Posted by JBeaucaire

clear the cell so that it is empty....

That's good, you did that.

Originally Posted by JBeaucaire

...then lock your sheet.

The sheet isn't locked....

Originally Posted by JBeaucaire

On the Menu Bar just to the left of FILE : EDIT : VIEW : INSERT you will see an Excel icon. Right-click on it and select VIEW CODE. This will open the WorkBook Module for you. Now, paste in this code

...in the next post:
3. Locate ThisWorkbook in the Project Window on the left...if it's not open press Ctrl-R to open it
4. Copy and paste in the code

I opened the ThisWorkbook Module and it's empty. Seems you pasted it into the Sheet Module for Sheet1. That's not right... hehe. WorkBook_Event macros won't work in sheet modules, and vice versa.

7. Save your sheet as an Excel TEMPLATE

The sheet you sent me wasn't saved as a template, it was an XLS.

So...that's not a good score for doing everything I told you. 1 out of 5...ouch! (prod-poke)

Anyway, I added a password to your sheet (barpwd) which means I needed to tweak that macro just a bit anyway. Here it is as a template. This means each time you open it, you will be opening a COPY, not the actual original template. So, put the file in your templates directory or on your Desktop where you can double-click it directly.

You'll know it's opened properly when it opens with the name BarMaster1...Excel adds the number.

Originally Posted by bbarnes81

ok ok digs taken on the chin

hehe...I love a good ribbing! Glad you've got a sense of humor, not everyone around here does... (glances about nervously)

But - when I saved as a template and tried to upload it said invalid file.

YEAH! I noticed the same thing after my response...I had to ZIP the file to get it to upload. Very odd that .XLT is not an accepted file extension.

Now that you have sent the sheet - thanks very much, there are other sheets part of it, will I be able to add these in without any hassle??

Um...I don't know, as long as it's not a "follow directions" process, you'll do fine. (hee-haw)

Actually, all kidding aside... Keep in mind when you save the template to reset the values in B1 and relock the sheet. Even opening the template itself to update it will trigger that workbook_open event. Eh?

I always meant you to use the xltm version for Excel 2007. I can't do that with my version, but you can.

I hope I didn't leave an external link of some kind in there... check EDIT > LINKS.

I thought the sheet always looked for an EXPENSES sheet of some particular name...down in the EXPENSES column. Reset those formulas to what you need them to be