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
"Joel" wrote:
> 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" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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
|
Similar Threads
