Automatically select correct range

[WLMPilot]

Within one worksheet, I have a "form" (for lack of a better word) that
represents a year. I copy the previous year and paste below it for the
current year data to be input. The same macro is used to enter that data
into each year. I do not want to enter directly in the spreadsheet, but
rather via a userform.

SPREADSHEET SETUP: The spread sheet is desiged to indicate the date I get
paid. Since I get paid, say on Jan 4, then the days that I worked are in the
previous year.
With that in mind, this is what Column A (Week #) & Column B (date) looks
like. Each date is the date for each Monday of the year.

A B
50 12/17/07
51 12/24/07
52 12/31/07
1 1/7/08
2 1/14/08
3 1/21/08
continues to
49 12/8/08

Here is my question/problem. There will be multiple forms within a
worksheet, one for each year. I need the macro to somehow know which year
(form) is current so that when a user enters the date worked in textbox1,
there will be a check to verify that the date is valid for current year.
Remember the current year of work will include a couple of weeks or so from
the end of the previous year.

Thanks,
Les

 

[Joel]

The simpliest way is to add a new column (or cell at top of form) that
contains the sheet name or the fisical year which is part of the sheet name.
You could also add at the top of the form a year start date and year end date
and make sure the entered date fall between these tow dates.

 

[WLMPilot]

Reading your reply got me thinking. If I NAME the range, ie 2007, 2008, etc,
could I make the check based on YEAR(Date)? If this can be done, Then I need
to set the range or determine an OFFSET value based on the NAME value.

How is a NAME referenced within a macro?

Thanks,
Les

 

[Joel]

Excerl won't except 2007 or 2008 as a name range. but you could have _2007
or _2008.

Then Range("_" & Year(Date)). But the problem is how do you know when the
form spans two years which is the correct year? The formula for figuring out
if a week is within the last two weeks of a year is complicated because
Monday can be any of 7 dffierent dates. To keep things simple I recomment on
the form to have the Start Date and End Date.

 

[WLMPilot]

I manually create (copy/paste previous) the form. The first date is set and
the remainder is calculated via adding 7 to previous cell (date). I already
have the formula that can calculate the placement of hours worked using the
date worked (entered on userform) and the dates in the spreadsheet (column
B). I just need to be able to tell which calender year is current and check
it against the date entered to determine if valid. Once I determine which
form is current, then I can determine OFFSET.

Thanks,
Les